MS Office User

Calculated Data Types in Microsoft Access 2010

by on Aug.01, 2012, under Access, Office

Storing calculated values in tables is not usually good practice, however once in a great while there may be a valid reason to do so.  Microsoft Access 2010 has the ability to calculate and store a value in the table by using the Data Type Calculated.

For example: (See Video above for Step by Step)
There is a client requirement to have separate Date and Time controls in a data entry form.

If there will be a need in the database application to calculate Time Intervals (i.e. Age in yrs,mos, days, Elapsed Time, Projected Dates & Time, etc.) a single Date and Time field will be required to complete the calculations.

In prior versions of Access this would have required creating expressions in queries to add the two fields together.
Microsoft Access 2010 offers the option to create the expression at the table level.

  1. In Design View of the table enter the Field Names
  2. Save the Table
  3. Enter the Name of the Field which will be Calculated
  4. Select Calculated as the Data Type
  5. The Expression Builder will pop up
  6. Build the expression
  7. Save the table

The Calculated Field value will now be available to use in queries or for display on forms and reports.

Word of Caution:

Use the Calculated Data Type selectively, sparingly and ONLY when the Components in the expression will remain STATIC.  That is the Components will NEVER change.

  • An Individual’s Date and Time of Birth will never change.
  • The Discount Percentage on a order may possibly change over time and therefore ALL values in a Calculated field will reflect a change made to any of the components of the expression.  Any previous data would then be erroneous.

The Calculated Data Type is not meant to replace queries.  Think of it as a handy time saving gadget—like a remote car starter.  Great option to have but not a necessity or the only way to start the engine.

Comments Off :, , , , , , , more...