Lesson · 24 min

Building Select Queries

Ask questions of your data using the query grid, and understand the SQL Access writes underneath it.

By the end of this lesson you can

Create a select query using the query design grid
Filter with criteria and sort results
Query across related tables using a join
Read the SQL behind a query you built visually

📘 Reading Lesson

Lesson Notes

Read through the key concepts before you try the challenge.

A query is a saved question

On the job

You answer questions from the practice manager at Lakeside Medical Associates.

'Which supplies are below reorder level?' 'Which patients saw Dr. Okafor last month?' 'What did we spend by category?' Each is a question about data you already hold. Scrolling the tables to answer them by eye is slow and gets a different answer each time.

Your task: Build queries that answer a question correctly every time they run.

A query does not store data. It stores a question, and runs it against current table data each time you open it. Add a record tomorrow that matches the criteria and it appears in the query results without anyone editing the query.

Row	Sets
Field	Which field this column shows
Table	Which table it comes from
Sort	Ascending or descending
Show	Whether the column appears in results — uncheck to filter on a field without displaying it
Criteria	The condition a record must meet
or	An alternative condition

The query design grid

Criteria on the same row are combined with AND — every condition must be true. Criteria on different rows are combined with OR — any one may be true. This is the single most common source of queries that return far too many or far too few records, and it is entirely a matter of which row you typed in.

Expression	Matches
"Medical"	Exactly that text
<10	Values below 10
Between #1/1/2026# And #3/31/2026#	Dates in that range, inclusive
Like "Glove*"	Text starting with Glove
Is Null	Empty fields
Not "Inactive"	Anything except that value
[Enter category:]	Prompts the user — a parameter query

Criteria expressions

Every query you build in the grid exists as SQL, viewable through View > SQL View. Reading it is worth learning: a complex query is far easier to understand as eight lines of text than as a grid spanning four tables, and the same SQL fundamentals work in every other relational database you will ever meet.

SELECT ItemName, Category, Quantity, ReorderLevel
FROM Supplies
WHERE Quantity < ReorderLevel
ORDER BY Category ASC;

Worked example

Answering 'which supplies are below reorder level?'

Build a query listing every item whose quantity on hand has fallen below its reorder level, grouped sensibly for ordering.

1
Create > Query Design, and add only the Supplies table.
Add only the tables you need. Extra tables without a defined relationship produce a cross join, which multiplies every row against every other and returns nonsense — usually thousands of rows where you expected twelve.
2
Add ItemName, Category, Quantity, and ReorderLevel to the grid.
Include the fields the person reading the result needs to act. A list of item names alone tells them what to order but not how urgently or from which category.
3
In the Quantity column's Criteria row, enter <[ReorderLevel].
Comparing one field to another is what makes this a real rule rather than a fixed threshold. A hard-coded <10 would be wrong for every item whose reorder level is not 10, and would silently go stale as levels changed.
4
Set Sort to Ascending on Category.
Sorting groups the results the way the person ordering will work through them — by supplier category. Sort order is part of making a result usable rather than merely correct.
5
Switch to SQL View and read what Access wrote.
Confirming the generated SQL matches your intent builds the fluency that makes complex queries debuggable later. It takes five seconds and is the fastest way to learn to read SQL.

Result: A query that answers the reorder question correctly every time it runs, against current data.

Compare fields to fields rather than to fixed numbers, add only the tables you need, and read the SQL to confirm intent.

Check your understanding

You enter criteria in two different columns on the same Criteria row. How does Access combine them?

Challenge

Apply what you've learned in this lesson.

Answer real questions with saved queries.

Build a query listing supplies below their reorder level, sorted by category. Open SQL View and copy the statement Access generated.
Build a query across two related tables — for example, appointments with the patient's name. Confirm the join line appears in the design view.
Build a parameter query prompting for a category, and test it with three categories.
Deliberately put two criteria on the same row, note the row count, then move one to the 'or' row and note it again. Explain the difference.

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

Calculated Fields and Totals Queries →