Creating Select Queries and Calculated Results

Ask useful questions of one or more tables with criteria, joins, sorting and calculations.

What you will learn

• Build a select query in Design view
• Use criteria and joins correctly
• Create calculated fields and totals

Before you begin

Use a practice database containing non-sensitive sample information. Access versions can place commands differently, so use the ribbon search box when a named command is not immediately visible. Back up the file before structural changes.

Build a select query

Add the necessary tables, choose only fields needed in the result and set a meaningful sort order. Run the query to see its datasheet.

A query displays data from its sources rather than creating a second independent copy. Saved queries can support forms, reports and later queries.

Write criteria

Criteria limit rows. Text, dates and numbers require appropriate syntax, while And and Or express different logical conditions.

Test one condition at a time and inspect the result. Incorrect criteria can return too many, too few or no records without producing an error.

Calculate and summarise

A calculated field derives a value such as Quantity multiplied by UnitPrice. Give the expression a clear alias.

Totals queries group records and calculate Sum, Count, Average, Minimum or Maximum. Confirm the grouping level before trusting a summary.

Guided practice

1. Create a query listing customer names and their orders.
2. Add a date or amount criterion.
3. Create one calculated field or totals query and verify it manually.

Knowledge check

1. Does a select query duplicate its source data?
2. What is the difference between And and Or?
3. Why must grouping be checked in a totals query?

Completion standard

Continue when you can complete the guided practice safely and explain the knowledge-check answers in your own words.