By the time you reach Level-3 of the MS Access series, you’ve already built solid tables with proper rules and validations. Now it’s time to put those tables to work. And one of the most satisfying things you can do in Access is let queries do the math for you — automatically.
This guide is based on the Skill Header MS Access Tutorial Level-3, which covers auto calculation methods using simple queries. If you’ve been building a database manually and doing calculations outside of Access (in Excel, on paper, or in your head), this is the part that changes everything.
What Is an Auto Calculation in a Query?
When you run a query in MS Access, you’re not limited to just pulling data from a table. You can also perform calculations on that data — right inside the query. The result appears as a new column in your query output, but it doesn’t get stored in the table. Access recalculates it fresh every time you run the query.
This means your numbers are always up to date. Change a price in your table, rerun the query, and the totals reflect the new value instantly. No formulas to update, no manual work.
Building the Query — Starting Simple
Before adding any calculations, you start with a basic Select Query in Design View. You choose the table you want to work with, drag in the fields you need, and run it to see your data.
This is the foundation. Once the query is pulling the right records, you start layering in calculated fields on top.
Calculated Fields in Queries
A calculated field is a column you define in the query grid that doesn’t exist in your table — it’s computed from other fields.
The syntax looks like this:
FieldLabel: [Field1] * [Field2]For example:
TotalPrice: [Quantity] * [UnitPrice]You type this directly into an empty column in the query design grid. Access reads it, performs the calculation for every row, and displays the result as a new column called TotalPrice.
You can use any standard math operators — addition (+), subtraction (-), multiplication (*), and division (/). You can also combine multiple fields in a single expression.
Using the Expression Builder
If you don’t want to type expressions by hand, Access has the Expression Builder — a visual tool that helps you construct formulas without memorizing syntax.
Click into any empty field cell in the query design grid, then open the Expression Builder from the ribbon. You’ll see a panel where you can browse your table fields, built-in functions, and operators. Click to insert them, and the builder assembles the formula for you.
It’s especially helpful when your calculations get more complex — involving functions, nested conditions, or fields from multiple tables.
Aggregate Functions — Sum, Count, Average, Min, Max
So far we’ve looked at row-level calculations — calculations that happen for each individual record. But sometimes you need to summarize across all records. That’s where aggregate functions (also called Totals) come in.
To enable them, click the Totals button (the sigma symbol Σ) in the query design toolbar. A new “Total” row appears in the design grid. For each field, you can now choose how to summarize it:
| Function | What It Does |
|---|---|
| Sum | Adds up all values in the field |
| Count | Counts how many records exist |
| Avg | Calculates the average value |
| Min | Returns the smallest value |
| Max | Returns the largest value |
| Group By | Groups records together by that field’s value |
For example, if you want to know the total sales per product category, you’d set the category field to Group By and the sales field to Sum. Access groups all records by category and adds up the sales in each group.
Group By — Summarizing by Category
Group By is what makes aggregate queries so powerful. Without it, you’d just get one total for everything. With it, you get a breakdown.
Say you have an orders table with hundreds of rows. You want to know: how much did each customer spend in total?
- Set the customer name field to Group By
- Set the total amount field to Sum
Run the query. Access collapses all the rows per customer into a single summary row — showing each customer once, with their total spend next to their name.
This works for any category: product type, department, region, date range, sales rep — whatever field makes sense for your data.
Combining Calculated Fields with Totals
Here’s where it gets really useful. You can use a calculated field as the basis for an aggregate — not just table fields.
For example, if your table has Quantity and UnitPrice but no total column, you can:
- Create a calculated field:
LineTotal: [Quantity] * [UnitPrice] - Set that field’s Total to Sum
Now your query automatically multiplies quantity by price for each row, then sums up those totals — all in one go. You never need to store the intermediate value in your table.
Formatting Your Results
Numbers in query results don’t always look the way you want out of the box. A currency total might show up as a plain number, or a percentage might appear as a decimal.
Right-click on any field in the query design grid and choose Properties. From there, you can set the Format (Currency, Percentage, Fixed, Standard, etc.) and the number of decimal places. Your query results will now display the values in a clean, readable format — without changing the underlying data.
Sorting and Filtering Aggregated Results
Once you have totals and grouped data, you’ll often want to sort or filter the results.
- Sorting: Click the Sort row in the design grid for any field and choose Ascending or Descending. For example, sort by Sum of Sales descending to see your top-performing products first.
- Filtering with Having: When filtering aggregated results, Access uses a special row called Where or Having (depending on when the filter applies). For instance, you could filter to only show customers whose total orders exceed a certain amount.
This lets you build queries that answer real business questions — not just “what’s in the table” but “who are my top customers” or “which products are underperforming.”
Why Do Calculations in Queries Instead of Tables?
You might wonder: why not just add a calculated field directly in the table (like we did in Level-1 and Level-2)?
There are a few good reasons to do calculations in queries instead:
Flexibility — A query can pull from multiple tables and combine fields in ways a single table can’t.
Aggregation — Tables can’t sum or group records. Queries can.
No redundant data — Storing a calculated value in a table means updating it every time a source field changes. A query recalculates on the fly — always fresh, never stale.
Reusability — Save a query once and run it any time. Change the underlying table data, and the query results update automatically.
A Practical Example
Let’s say you’re running a small shop and you have an Orders table with these fields: CustomerName, ProductName, Quantity, UnitPrice.
With a query, you can:
- Add a calculated field:
OrderTotal: [Quantity] * [UnitPrice] - Enable Totals, group by
CustomerName, and sumOrderTotal - Sort by sum descending
- Filter to only show customers whose total is above a threshold
In under five minutes, you have a ranked list of your top customers by spending — built entirely from a simple table, with no extra columns, no manual calculations, and no Excel needed.
Quick Reference
| Technique | How to Use It |
|---|---|
| Calculated Field | Type Label: [Field1] * [Field2] in an empty query column |
| Expression Builder | Right-click a field cell → Build |
| Totals Row | Click Σ in the toolbar to enable aggregation |
| Sum / Avg / Count | Select from the Totals dropdown for numeric fields |
| Group By | Assign to any field you want to group results by |
| Format Results | Right-click field → Properties → Format |
| Sort Results | Use the Sort row in the query grid |
Final Thoughts
Queries are where MS Access earns its reputation as a proper database tool — not just a fancier spreadsheet. Once you understand calculated fields and aggregate functions, you stop thinking of your database as a place to store data and start thinking of it as a place to analyze data.
The combination of Level-1 (building tables), Level-2 (enforcing rules), and Level-3 (auto calculations in queries) gives you a complete picture of how a real database is designed and used from the ground up.
Watch the full MS Access Tutorial Level-3 on YouTube by Skill Header and download the practice files at skillheader.com to follow along.
