Calculated Fields and Totals Queries
Compute values in queries rather than storing them, and summarize grouped data with aggregate functions.
By the end of this lesson you can
- Build a calculated field using an expression
- Explain why calculated values do not belong in tables
- Use a Totals query with Group By and aggregate functions
- Choose the right aggregate for a question
Lesson Notes
Read through the key concepts before you try the challenge.
Replacing a stored total
Removing a stored TotalCost column
A previous employee added a TotalCost field to the table and typed the values in. Replace it with a calculation that cannot go stale.
- 1
Before deleting anything, build a query with the calculation and compare it against the stored column.
The comparison tells you how far the stored values have already drifted, and it is worth knowing before you remove the evidence. If they all match, nothing has changed since they were typed. If dozens differ, the table has been quietly wrong and someone may have made decisions on it.
- 2
In the query grid, add a new field: TotalCost: [Quantity]*[UnitCost].
The name before the colon becomes the column heading; the expression after it is evaluated per row, every time the query runs. Square brackets are how Access refers to a field, and they are required when a name contains a space.
- 3
Format the calculated field as Currency in its property sheet.
A calculated field has no inherited format, so it displays as a raw number — 41.97 rather than $41.97. Setting the format on the query field fixes it everywhere the query is used, including any report built on it.
- 4
Point any forms and reports at the query rather than the table.
This is the step that is easy to miss. Deleting the table field breaks every object still bound to it. Repoint them first, confirm they work, then delete.
- 5
Delete the stored field from the table, on a copy of the database first.
Deleting a field deletes its data irreversibly. Work on a copy until you have confirmed nothing else referenced it — Access will not warn you about a report you forgot.
Result: One calculation, always current, with nothing left in the table that can contradict it.
Compare before you delete, repoint dependent objects first, and format the calculated field — an unformatted currency column looks broken.
Calculate, do not store
You maintain the supply database at Lakeside Medical Associates.
Someone added a TotalCost column to the table and typed the values in. A unit price changes. The quantity changes. TotalCost does not, because it is a number someone typed six weeks ago. The table now contradicts itself, and nothing on screen indicates it.
Your task: Compute derived values in queries so they cannot go stale.
A calculated field computes a value from other fields each time the query runs. In the query grid you write it as a new field: TotalCost: [Quantity]*[UnitCost]. The name before the colon becomes the column heading, and the expression after it is evaluated per row.
| Function | Returns | Answers |
|---|---|---|
| Group By | The field to group on | Per what? — per category, per provider |
| Sum | The total across the group | How much altogether? |
| Avg | The mean | What is typical? |
| Count | How many records | How many are there? |
| Min / Max | Smallest or largest | What is the range? |
| Where | A filter applied before grouping | Which records should count at all? |
A Totals query collapses many rows into one per group. Click the Totals button on the Design tab and a Total row appears in the grid. Set the field you are grouping by to Group By, and the field you are summarizing to Sum, Count, or whichever aggregate answers the question.
Why should an item's total cost be calculated in a query rather than stored in the table?
Challenge
Apply what you've learned in this lesson.
Replace stored values with computed ones.
- Add a calculated field TotalCost multiplying quantity by unit cost. Change a unit cost in the table and confirm the query result updates.
- Build a Totals query grouping by category with Sum of TotalCost and Count of items.
- Add a Where condition so the totals include only items ordered this year, and confirm the numbers change.
- Write two sentences explaining to a colleague why you removed the stored TotalCost column from the table.
Finished this lesson?
Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.