Results 1 to 9 of 9
  1. #1
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    94

    Calculated columns


    I want an advice from an expert.
    I have a table that includes multiple calculated columns that depend on other calculated columns in the same table.
    All the calculated expressions are direct additions, subtractions and multiplications.

    I read that calculated columns inside tables are not recommended, is this true?
    What are the drawbacks of this design? What happens when the table size increases?
    Is there a risk of wrong calculations or data corruptions in the table?
    What about the forms related to this table, will they become slower?

  2. #2
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    806
    Hi,
    True: tables are structures that store the base data. In principle calculations are made in queries or on forms/reports.
    If you store the calculated values in a table, they need to be calculated in every query, form or report you build on the table, even if the field is not used in that qry/form/report. This is slowing down searches. Better to make the calculation only in places you really need them.
    Storing a lot of calculated values will blow up the size of your database and make it even slower.
    The bigger the database file, the more chances of corruption. You might have to compact and repair your database on regular moments.
    Honestly: having calculated columns depending on other calculated columns is my idea of a nightmare.

  3. #3
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,040
    In addition to what NoellaG has stated, there is another important consideration, Calculated columns can't be indexed.
    So if you have a large data set any searches on those calculations will become very slow.

    There are places for Calculated fields - but they are few and far between.
    I frequently use one for FullName = [FirstName] & " " & [LastName] , but that is in a table that has at most a few hundred records, and just simplifies making combobox lookups.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    94
    Unfortunately, removing calculations from columns is very exhausting because they are used everywhere in my database.
    I was thinking of the following solution:
    I create another table that is similar to the first one, but all its columns are normal (uncalculated) and at the end of every day, I click a button that moves all records from the first table to the second table.
    In this case, the first table with calculated columns will not exceed 1000 records daily.
    And then I do all my reporting on the second table ( or first table union second table if I need to show a report before the end of the day.
    And finally I do a daily backup and I compact and repair my database once every month.
    Do you think this will work? Do I still have risk of corruptions? What about the performance of the first table when the number of records does not exceed 1000 records?

  5. #5
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,040
    Why not simply have a query that produces all the calculations from the base data, and filter it's output to the last day/week/month based on your requirements?
    You can include all the original data points in the query output as well so you wouldn't be messing anything up.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    NoellaG's Avatar
    NoellaG is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    806
    Hi,
    just as you don't (or in very few cases) use calculatedfields in tables, you seldom base your reports on tables. Normally one creates queries that get the required data from several tables, combines them and filters them, calculates what needs to be calculated and presents the data to the report. In the report itself you can do the sorting and maybe some further calculations.
    The phrase
    or first table union second table if I need to show a report before the end of the day
    sends shivers along my spine.
    Just remove the calculations from your table and create queries with the calculations like Minty suggested. Change the recordsource of the objects where you use the calculated fields to the name of the query. Even if you have over 100 database objects this will be faster than the solution you mentioned in post #4

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,423
    Maybe this article will convince you NOT to use Calculated Fields in tables (different than calculated columns in a query) About calculated table fields
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,279
    The same arguments go for storing calculated values ( ie your other table) With very few exceptions calculated values should not be stored - exceptions are typically around calculations that have legal implications such as those used in generating an invoice

    Sounds like you are trying to apply excel methods to a database. They are at opposite ends of the data management spectrum. Trying to use those methods on a database is at best unwieldy and difficult to manage and at worst is doomed to failure

  9. #9
    bilalo is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    94
    Thank you all for the advice, based on your feedback I will remove calculated fields from the table and move calculations to reports.
    My sincere respect for all of you

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

Similar Threads

  1. Help query with multiple calculated columns
    By Zaccariotto in forum Queries
    Replies: 5
    Last Post: 05-04-2020, 07:06 AM
  2. Replies: 11
    Last Post: 07-26-2019, 07:31 AM
  3. Replies: 1
    Last Post: 05-22-2017, 05:59 AM
  4. Calculated Columns Same Table And "Column Scrubbing"
    By defaultuser909 in forum Queries
    Replies: 10
    Last Post: 07-19-2012, 11:42 AM
  5. Calculated fields / Computed columns
    By goodguy in forum Programming
    Replies: 11
    Last Post: 09-18-2011, 02:18 PM

Tags for this Thread

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