Creating Queries in Microsoft Access: A Practical Guide

A query is how you ask an Access database a question. Which orders shipped late, which customers have not bought since March, what did we sell by region last quarter. Everything useful a database does for a business comes out through queries.

This is a practical guide to writing them, including the traps that catch people building their first ones.

The four types you will use

• Select — retrieves data. This is almost everything you will ever write, and it changes nothing.
• Update — changes existing records in bulk.
• Append — adds records to a table.
• Delete — removes records.

The last three are action queries. They alter data permanently and there is no undo. Always build one as a Select query first, look at exactly which rows come back, and only then change it to an action query. Back up before running one for the first time. This single habit prevents the worst thing that happens to Access databases.

Building a select query

Use Create → Query Design, add the tables you need, and drag the fields you want into the grid. If relationships are set up properly, Access joins the tables for you.

Each column in the grid gives you: which field, whether it shows, how to sort, and the criteria that filter it.

Criteria that actually get used

• >100 — numbers above a value; Between 10 And 20 for a range.
• "Cincinnati" — exact text match.
• Like "Smith*" — starts with. Access uses *, not the SQL %, which trips up anyone arriving from SQL Server.
• Is Null — the field is empty. Genuinely different from an empty string, and a common source of confusion.
• >Date()-30 — the last 30 days.
• [Enter customer name] in square brackets — makes it a parameter query that prompts the user, so one query serves many questions.

Criteria on the same row are joined with AND. Criteria on different rows are joined with OR. Getting this backwards is the most common reason a query returns nothing.

Joins, and the one that catches everyone

By default Access uses an inner join — only rows with a match on both sides. Ask for customers and their orders and you will silently lose every customer who has never ordered.

If you want all customers regardless, double-click the join line and choose the option that includes all records from the customers table. That is an outer join, and it is the answer whenever a report is missing rows nobody can account for.

Calculations and totals

Create a calculated field by typing LineTotal: [Quantity]*[Price] into an empty column. The part before the colon is the name.

Calculate rather than store. A stored total goes stale the moment a quantity changes; a calculated one cannot.

For summaries, click Totals on the ribbon to get a Total row, then choose Group By on what you are grouping and Sum, Count or Avg on what you are measuring. Sales by customer, orders per month, average value by product — all the same pattern.

Traps worth knowing

• Query returns nothing. Usually criteria on the wrong rows (AND when you meant OR), or an inner join dropping the rows you wanted.
• Duplicated rows. Usually a join to a table with multiple matching records — one order with three lines gives three rows. Correct behaviour; group it if you want one row per order.
• Slow queries. Index the fields you filter and join on. Without indexes, Access reads every row.
• Text that will not match. Trailing spaces. Trim() is your friend.
• Action query run twice. An append run twice inserts everything twice. Check before re-running.

Where queries stop helping

Queries are the strongest part of Access and they will answer almost anything about your own data. What they cannot do is get around the format's limits: 2GB per file, roughly ten concurrent users, and no browser or mobile access since Access Web Apps were discontinued in 2018.

If your queries are correct but people cannot reach them from outside the office, or everything slows when several users are in at once, that is a platform problem rather than a query problem. Moving the tables to SQL Server while keeping Access as the front end fixes the size and concurrency limits without rebuilding anything; a web application fixes the rest.

Either way the query logic is not wasted — it is the specification for whatever replaces it. Our Microsoft Access services page explains both routes, and a free system review will tell you which one you need.