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.

Random Posts:

  1. Creating a new blank database using Microsoft Access 2007
  2. Showing Hidden and System Objects in the Navigation Pane
  3. Creating Primary Keys and Foreign Keys in Microsoft Access
  4. How to Create the USysRibbons table in Microsoft Access 2007
  5. How to Enable the Developer Ribbon in Microsoft Excel 2010
  6. Top 10 Reasons to get Microsoft Office 2010!
  7. Submitting Access Database Templates to Office Online
  8. Creating a Database from Template using Microsoft Access 2007
  9. Using the Graphics Manager to work with Images in Publisher 2007
  10. The new site is up and running!

:, , , , , , ,

Comments are closed.