Lesson · 22 min

Parameter Queries and Calculated Fields

Build queries that prompt for input and that compute values on the fly, so one query answers many questions.

By the end of this lesson you can

Create a parameter query that prompts the user for a value
Build a calculated field using an expression
Explain why calculated values should not be stored in tables
Use aggregate functions to summarize grouped data

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

One query instead of twelve

On the job

You support reporting at Lakeside Medical Associates.

The practice manager wants supply usage by month. You build a query for January. Then February. By June you have six nearly identical queries in the Navigation Pane, differing only in a date, and each one is a thing that can fall out of date independently of the others.

Your task: Build one query that asks which month you want, rather than one query per month.

A parameter query prompts for a value when it runs and uses the response as a criterion. Instead of hard-coding a date, you put a prompt in square brackets in the criteria row, and Access asks the user for it each time.

SELECT ItemName, Category, Quantity, OrderDate
FROM Supplies
WHERE OrderDate BETWEEN [Enter start date:] AND [Enter end date:]
ORDER BY OrderDate;

The bracketed text is not a field name — Access recognizes that and treats it as a prompt, showing it to the user as a dialog. One query now covers every date range anyone will ever ask for.

A calculated field computes a value from other fields rather than storing it. 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 for every row.

Never store a calculated value in a table when you can compute it in a query. A stored TotalCost becomes wrong the moment someone edits Quantity or UnitCost, and nothing warns you — the table now contains a number that contradicts its own inputs. Calculate it in the query and it is correct by construction, every time it runs.

Function	Returns
Sum	The total of a numeric field across the group
Avg	The mean value
Count	How many records are in the group
Min / Max	The smallest or largest value
Group By	The field to group the results by
Where	A filter applied before grouping

Aggregate functions in a Totals query

Check your understanding

Why should a supply 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 repetitive queries with one flexible query.

Build a parameter query on your supplies table that prompts for a category and returns all items in it. Test it with three different categories.
Add a calculated field TotalCost multiplying quantity by unit cost. Then change a unit cost in the table and re-run the query to confirm the total updates.
Build a Totals query grouping by category, with Sum of TotalCost and Count of items. Note which rows are grouped and which are aggregated.
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.

← Understanding SQL Statements Module 7: Advanced Queries & Reports →