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

    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    louise is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2015
    Posts
    112
    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Post back if you get stuck. Good luck!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    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
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    @ 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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    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
    112
    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
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,387
    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 offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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