Constructing a calculated query in Microsoft Access 2019

It is possible to perform calculations while constructing a query.You can add a new field to the query that calculates, for instance, a20% markup on products or a 30% increase in outstanding accountpayments. As long as you have existing fields within the table on whichto base the calculation, it’s extremely easy. To construct such a query,follow these steps:

  1. Open the ElkAdmin.accdb database. Locate the OutstandingAccounts query and double-clickto open it in Datasheet View. Take afew seconds to look at the data provided in the query.
  2. View the query in DesignView.
  3. Add a new field called CHARGES tothe query grid to the right of the OUTSTANDING field. The field must calculatea 20% increase in the outstanding amount, as illustrated in thefollowing screenshot:

ms office 121

  1. There are two methods to enter the calculation into the field,as follows: 
  • The first method is to create a new field in thecolumn alongside OUTSTANDING. You mustinsert a colon after the new name—for example, CHARGES. When using fields incalculations, you need to surround the field name with squarebrackets—for example, [OUTSTANDING]. To work out 20% of theoutstanding amount, you would type *.2. Therefore, thecalculation would be as follows: CHARGES:[OUTSTANDING]*.2.
  • The second method would be to use the Builder button located on the Query Setup group to help formulate thecalculation. Click on the Builderbutton to launch Expression Builder,as illustrated in the following screenshot:
ms office 288

The dialog box contains Expression Elements such as thedatabase objects (tables, queries). Click onthe ElkAdmin.accdb element to expand the objectsunderneath it. Click to select the OutstandingAccounts query. FromExpression Categories, choosethe field or fields that you require to formulate your calculation bydouble-clicking on the field to place it into the Expression Builder, as illustrated inthe following screenshot:

ms office 225

Alternatively, type the expression yourself into the build areaprovided at the top of the dialog box. Remember to place a colon aftertyping the field name to separate the name from thecalculation.

  1. Click the OK icon once theexpression is complete.
  2. The expression appears on the query grid as a new column.
  3. To view the complete expression or make any changes, press theShift key on the keyboard. While this key is depressed, alsopress the F2 function key. A zoom window will open, showing theexpression with enough room for editing.
  4. Click on the OK icon to return tothe query design.
  5. Press the Run icon located on theResults group to see the result of thequery.
  6. Change the view back to DesignView to edit the query, if necessary.

Leave a Comment