20 min read
Sorting and Calculated Fields in Queries
Click the Sort row to reorder results, and type an expression like `ExtendedPrice: [UnitPrice]*[Quantity]` to build a brand-new field on the fly.
You will learn: how to choose columns and sort order in the grid. You will do: build MemberPhones. save MembersAZ sorted by name.
Choose Fields
Goal
Build a phone list with FullName and Phone only.
You do not have to show every column on the table.
Create a new query
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- The result shows FullName and Phone for all five members.
SOURCE DATA
| Members | |||
|---|---|---|---|
QUERY DESIGN
| Grid — MemberPhones | |||
|---|---|---|---|
RESULT
| Result: MemberPhones | |
|---|---|
Sort A–Z
Goal
List every member sorted by name and save as MembersAZ.
Sort in the query keeps the order each time you run it.
Add fields to the grid
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Members appear in ascending order by FullName.
QUERY DESIGN — MembersAZ
| Grid — MembersAZ | |
|---|---|
RESULT
| Result: MembersAZ | ||
|---|---|---|
Hide with Show
Clear Show when a field is needed for sort or criteria but should not appear in the result.
Example: sort by City but show only FullName and Phone — add City, set Sort to Ascending, clear Show on City.
Practice
Swipe or use the arrows to move between questions.
1 / 2
You will learn: calculated field syntax with alias and brackets. You will do: add DaysOut to open loans. add DueDate with DateAdd.
Expression Syntax
In an empty Field cell:
Alias: expression
Examples: - DaysOut: Date()-[LoanDate] — days since loan (Date() is today) - DueDate: DateAdd("d",14,[LoanDate]) — loan date plus 14 days
Square brackets around LoanDate mean the field from the query.
DaysOut on Open Loans
Goal
Show open loans with DaysOut calculated.
DaysOut is not stored on the table — Access works it out each run.
Add Loans
Where
Do this
Verification
Check your work
You are correct if all of these are true:
- Open loans show a DaysOut column.
- DaysOut updates when you run the query on a different day.
SOURCE DATA
| Loans | ||||
|---|---|---|---|---|
QUERY DESIGN
| Grid — OpenLoansDays | |
|---|---|
RESULT (sample — today = 10 Jun 2026)
| Result: OpenLoansDays | |||
|---|---|---|---|
DueDate with DateAdd
Library rule: books are due 14 days after LoanDate.
Field cell: DueDate: DateAdd("d",14,[LoanDate])
Add Members in Show Table to show FullName. Save as OpenLoansWithDueDate.
Practice
Swipe or use the arrows to move between questions.
1 / 2
Field Notes: Fields That Don't Exist in Any Table
In the query design grid, click the Sort row beneath any field and choose Ascending or Descending; results reorder by that field every time the query runs.
A query can also create a brand-new field, calculated from other fields on the fly. In an empty column of the design grid, type a name for the new field, followed by a colon, followed by an expression — for example: ExtendedPrice: [UnitPrice]*[Quantity]. Square brackets tell Access that a word refers to an existing field's name rather than plain text. The calculated field appears in the results just like any other, recalculated fresh from the current data every time the query runs.
More lessons in Microsoft Access · Next: Querying Multiple Related Tables · Previous: Adding Criteria to Filter Results