What a query does and why you need one

A query in Microsoft Access is a tool that pulls specific information from your database tables and shows you only what you asked for. Instead of scrolling through thousands of rows to find customers in one state or invoices from last month, you write a query that does the filtering for you. Access runs the query, and you get a clean list of just the records you need.

Queries are the reason databases are useful. Without them, a table with 50,000 customer records is just a wall of data. With a query, you can answer real questions: "Which orders are still unpaid?" or "How many products did we sell in March?" or "Show me all contacts in the Northeast region."

Access offers several ways to build a query. The simplest is the Query Wizard, which walks you through choices. The more flexible approach is Design View, where you build the query yourself. Both create the same thing — they just take different paths.

Key Takeaways

  • A query filters your database tables to show only the records and columns you need, without changing the original data.
  • The Query Wizard asks you questions about what you want to see and builds the query for you step by step.
  • Design View lets you manually select tables, columns, and conditions, giving you more control over what the query returns.
  • Once you create a query, you can run it again anytime — it always pulls the current data from your tables.
  • Queries can combine information from multiple tables, sort results, and count or sum values without writing code.

Using the Query Wizard for straightforward filtering

The Query Wizard is the fastest way to start if you know roughly what you want. Open your Access database, go to the Create tab at the top, and click Query Wizard. Access shows you a dialog asking which type of query you want — for most everyday work, choose straightforward Query Wizard.

The wizard then asks you to pick a table. Select the table that holds the data you're looking for — for example, if you need customer information, pick your Customers table. Next, you choose which columns to include. You'll see a list of all the fields in that table. Click the ones you want (like Name, Email, and City), then click the arrow button to move them to the "Selected Fields" side. Leave out the columns you don't need.

The final step asks whether you want to see all records or just certain ones. If you only want customers from California, choose "I want to define my own criteria" and the wizard will ask you to set conditions. Click Finish, and Access creates and runs your query. The results appear in a spreadsheet-like view showing only the rows and columns you selected.

Building a query in Design View for more control

Design View is where you have full control. Go to Create and click Query Design. Access opens a blank query window with a dialog asking which table to use. Select your table and click Add, then Close. You'll see the table name at the top of the window and a grid below it.

The grid has rows for Field, Table, Sort, Show, and Criteria. In the Field row, click the dropdown and select the first column you want to see. Do this for each column you need. If you want results sorted (like alphabetically by last name), click the Sort row under that field and choose Ascending or Descending.

To filter results, use the Criteria row. For example, if you want only unpaid invoices, find the Status field and type "Unpaid" in the Criteria row below it. You can add multiple conditions — if you want unpaid invoices from the last 30 days, add a date condition too. Once your grid is set up, click the Run button (the red exclamation mark icon) to see your results.

Combining data from multiple tables

One of the most powerful things a query can do is pull information from more than one table at once. This works when your tables are connected by a shared field — for example, a Customers table and an Orders table both have a Customer ID field.

In Design View, when you open the query, the dialog lets you add multiple tables. Click Add for each table you need. Access automatically detects the connection between them (the shared field) and draws a line showing the relationship. If Access doesn't find the connection, you can draw the line yourself by dragging the matching field from one table to the other.

Once both tables are in your query, you can select fields from either one. You might pull the customer's name from the Customers table and the order date and amount from the Orders table in a single result. This saves you from having to look up information in two separate places.

Adding calculations and summaries to your query

Queries can do math without you writing formulas. In Design View, you can count records, sum amounts, find averages, or find the highest or lowest value in a column. Click on an empty Field cell in the grid and type an expression like Count(*) to count all records, or Sum([Amount]) to add up all values in the Amount column. Replace "Amount" with whatever field you want to sum.

If you want to count orders by customer, you can use a Group By query. In the grid, set the customer name field to "Group By" instead of showing every row, and set your count field to "Count". Access will show you one line per customer with the total number of orders next to their name.

These summary queries are useful for reports and dashboards. Instead of manually counting or adding, the query does it every time you run it, so your numbers are always current.

Saving and running your query again

Once you've built a query and run it successfully, save it so you can use it again. Press Ctrl+S or go to File and click Save. Access asks for a name — use something clear like "Unpaid Invoices" or "Customers in CA". The query appears in your database's left sidebar under Queries.

Every time you click that query name, Access runs it again using the current data in your tables. If new records were added or old ones changed, the query results update automatically. You don't have to rebuild it or worry about the results getting stale.

You can also use a saved query as the source for a report or form. Instead of building a report from a table, you build it from a query, so the report only shows the filtered data you care about.

Common mistakes and how to avoid them

The most common mistake is forgetting to connect tables correctly. If you pull data from two tables but don't set up the relationship, you'll get confusing results or no results at all. Always check that Access drew a line between the matching fields, and if it didn't, draw it yourself by dragging.

Another mistake is setting criteria that are too strict. If you write "Status = Unpaid" but the field actually contains "UNPAID" in all capitals, the query returns nothing. Check your actual data first to see exactly how values are spelled and capitalized. You can also use wildcards — typing "Unpaid*" will match "Unpaid", "UNPAID", or "Unpaid Invoice".

Finally, don't assume a query is permanent. If you delete a query by accident, it's gone, but your original tables are safe. Queries don't change your data — they just show you a view of it. You can delete and rebuild a query without risk.

Frequently Asked Questions

Can I edit the results of a query directly?

Yes, in most cases. If your query pulls data from a single table, you can edit the results and the changes go back to the original table. If your query combines multiple tables, editing is usually blocked to prevent confusion about which table should be updated. Check whether the field is editable by trying to click it — if it's locked, you'll need to edit the original table instead.

What's the difference between a query and a filter?

A filter temporarily hides rows in a table you're already viewing. A query creates a new view that pulls specific data from one or more tables and saves it as a named object you can run again. Filters are quick for one-time looks; queries are for work you do regularly.

Can I use a query to change or delete records?

Yes, but you need a different type of query called an action query. straightforward queries just show data. Update queries change values, delete queries remove records, and append queries add new records from one table to another. These are more advanced and require careful setup to avoid mistakes.

How do I know if my query is working correctly?

Run the query and look at the results. Do you see the columns you wanted? Are the rows filtered the way you intended? If something looks wrong, go back to Design View and check your field selections and criteria. You can also run the query step by step — remove one condition at a time to see how it changes the results.

Can I create a query without using the wizard or Design View?

Not easily in the standard Access interface. The wizard and Design View are the built-in tools. If you're comfortable with SQL (the language databases use), you can write a query directly in SQL View, but that's beyond what most Access users need to do.