Results 1 to 12 of 12
  1. #1
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    109

    Calculated field in query vs table

    Hello,
    I have several calculated and concatenated fields that I use in many forms and reports.
    It would seem like the natural place to create these would be in the table that includes the component fields.
    But, it is my understanding that this is not the best practice, that it is better to do this in a query.

    Is this correct?
    And, if it is better to do it in a query, do you
    - recreate the field in every one of the many separate queries you use,
    - or do you have a "Master Query" that reproduces the entire table AND the calculated fields -- and use that in every separate query,


    - or do you have an "Add-On Query" that contains only these add-ons to the table, and then include that Add-On Query along with the table in each other separate query,
    - or .....

    Thanks for any advice.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,200
    Yes it is better done in a query. If you have many queries that use one field, I'm thinking that you are unnecessarily duplicating things. You don't create a query for each department/date range/manager etc. that returns the same info - you create one query and provide those parameters in some way. Same goes for reports and forms. If that's not your case then I suppose I might create a query that did my calculations and join that in other queries. Not something I've ever had to do because calculations in one query never had a relationship to those in other tables and I don't duplicate these objects.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  3. #3
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    109
    Thank you, Micron.
    You have given me some ideas for tightening up things.
    And, I appreciate your answer about the general approach.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,200
    Post back if you get stuck. Good luck!
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  5. #5
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    109
    Thanks! I have always appreciated my help from this forum!

  6. #6
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,148
    strongly recommend you do not go the master query route. For large amounts of data it will be very slow. Principle should always be to apply criteria first to limit the number of records returned before applying your calculations. A master query will do the opposite.

    A problem with calculated fields in tables - they can't be indexed so any query (with large amounts of data) using these fields for criteria or sorting will be slow.

    If you have many queries that use the same values, consider learning how to 'slice and dice' using VBA to create your sql string. A very very simple example


    Code:
    function mCustTable() as String
    
        mCustTable="tblCustomers"
    
    end function
    
    function mfullName() as String
    
        mFullName="[FirstName] & ' ' & [LastName]"
    
    end function
    
    function createSelectSQLwithCalc() as String
    
        createSelectSQLwithCalc="SELECT *, " & mFullName & " FROM " & mCustTable
    
    end function

  7. #7
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    780
    @ Ajax: just to have a better understanding: how can working with a master query slow down performance? I often create Views (I always work with SQL tables) for the end users so they don't have to bother with making the correct joins, correct index use, calculations. These views do not slow down performance, but tend to work faster, the more that I already can include some general restrictions as, for example, not including data from more than 3 years old. I understand that Access queries are a bit like SQL views. So, as long you don't include an order by clause, I don't see how a master query would be slower.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,200
    Yeah, I don't know about that either. First, I never suggested it. I said one query/report/form per task, not a query that loads every record from every field if that's what is meant by a master query.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

  9. #9
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,148
    my comment about master query is in response to the OP's first post

    @NoellaG - point is you are already including criteria to your view

    I already can include some general restrictions as, for example, not including data from more than 3 years old

    OP definition of a master query

    "Master Query" that reproduces the entire table AND the calculated fields

  10. #10
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    109
    So, Just so I'm clear...it seems like the best idea is to have a query that does the calculated fields that I want to use repeatedly, and then include that "adjunct query" in constructing subsequent queries, so I don't have to keep recalculating them. It would have only about a half-dozen fields, as well as the linking ids.

    Sound right?

  11. #11
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,148
    well, it wasn't what I was saying. Not sure it was what others were saying either.

    I was saying just create your calculated values as and when required in your query

  12. #12
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    9,200
    Why don't you provide some examples of the inputs and desired results? That could be pics or tables in your post or a complete db copy posted.
    Using terms like "adjunct query" leave room for interpretation because they have no explicit meaning in Access.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 01-08-2020, 10:11 AM
  2. Replies: 7
    Last Post: 03-14-2018, 04:14 PM
  3. Replies: 10
    Last Post: 06-10-2015, 08:16 AM
  4. Calculated Field In Table-Query-MakeTable Issue
    By Lisa Perry in forum Access
    Replies: 2
    Last Post: 02-13-2013, 02:00 PM
  5. Replies: 3
    Last Post: 02-13-2013, 10:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums