What a query does and when you need one

A query in Microsoft Access is a tool that pulls specific information from your tables and shows you only the rows and columns you want to see. Instead of scrolling through thousands of records in a table, you can ask Access to show you, for example, only customers from California who placed an order in the last month. Access finds those records, displays them together, and lets you sort, filter, or edit them as a group.

You build a query when you need to answer a specific question about your data. Queries are faster than manually searching, and they update automatically if your underlying data changes. Once you create a query, you can run it again without rebuilding it.

Key Takeaways

  • The simplest way to start a query is through the Query Wizard, which walks you through selecting tables, fields, and filter conditions step by step.
  • Design View lets you build queries by dragging fields into a grid and setting criteria, and is the method you will use most often once you understand the basics.
  • A query can pull data from one table or combine data from multiple related tables using joins.
  • You can sort query results by any column and add conditions so Access shows only records that match what you are looking for.

Starting a query with the Query Wizard

The Query Wizard is the fastest way to build your first query. Open your Access database, go to the Create tab at the top, and click Query Wizard. Access will ask you which table to pull data from. Select the table and click the arrow buttons to move field names from the left side (available fields) to the right side (selected fields). Choose only the columns you need.

After you select fields, the wizard asks whether you want to see all records or only certain ones. If you want to filter — for example, show only orders from 2024 — click Next and enter your conditions. The wizard calls these criteria. You can add multiple criteria, such as "Year equals 2024 AND State equals Texas". When you finish, the wizard saves your query and runs it immediately, showing you the results.

Building a query in Design View

Design View gives you more control and is where you will spend most of your time once you are comfortable with queries. To open Design View, go to Create and click Query Design. Access opens a blank query window and asks you to choose a table. Select your table and click Add, then close the dialog.

You will see your table name at the top of the window and a grid below it with rows labeled Field, Table, Sort, Show, and Criteria. Drag field names from the table down into the Field row of the grid. Each column in the grid represents one field in your results. If you want to sort by a field — for example, show results in alphabetical order by last name — click the Sort cell under that field and choose Ascending or Descending.

To filter your results, click the Criteria cell under the field you want to filter and type a condition. For a text field, type the exact value in quotes, like "California". For a number or date field, you can use comparison operators: type >100 to show values greater than 100, or >=2024-01-01 to show dates from January 1, 2024 onward. When you are ready to see your results, click the Run button (the red exclamation mark icon) or press Ctrl+Enter.

Combining data from multiple tables

Many databases split information across related tables — for example, a Customers table and an Orders table. A query can pull data from both at once if the tables are connected by a relationship. In Design View, when you add your first table, click Add Table again and select the second table. Access automatically draws a line between the tables if a relationship exists.

If no line appears, the tables are not yet related. You will need to create the relationship first by going to the Database Tools tab, clicking Relationships, and dragging the matching field from one table to the other. Once the relationship is set, add both tables to your query and drag fields from either table into your grid. Access will return only records where the tables match — for example, orders that belong to a customer in your Customers table.

Using operators and wildcards in criteria

Access understands several types of conditions. Use = for exact matches, < or > for less than or greater than, and <> for "not equal to". You can combine conditions with AND or OR. For example, type >50 AND <100 to show values between 50 and 100, or "New York" OR "California" to show records from either state.

For text searches, use the asterisk * as a wildcard to match any characters. Type "Smith*" to find Smith, Smithson, Smithers, and any other name starting with Smith. Type "*son" to find any name ending in son. If you want to search for a date range, type Between #1/1/2024# And #12/31/2024# to show records from that year.

Saving and reusing your query

When you run a query, Access asks you to name it. Give it a descriptive name like "2024 California Orders" or "Customers Without Phone Numbers". Access saves the query in your database and lists it in the left panel under Queries. Click the query name anytime to run it again with the same settings. The results update automatically if your underlying table data has changed.

To edit a saved query, right-click its name in the left panel and select Design View. Make your changes and click Run to preview the results. Click the Save button or press Ctrl+S to save your changes. If you want to keep the original query and create a new version, go to File, click Save As, and give it a new name.

Common mistakes and how to fix them

The most common mistake is forgetting to uncheck the Show checkbox for fields you want to use in criteria but do not want to see in your results. For example, if you filter by State but do not care about seeing the State column in your output, uncheck the Show box under that field. Your results will be narrower and easier to read.

Another frequent issue is using the wrong data type in your criteria. If a field stores dates, do not type 2024; type #1/1/2024# instead. If a field stores text, put quotes around your criteria: "Texas", not Texas. Access will show an error or return no results if the data type does not match. If your query returns zero records when you expect results, double-check your criteria spelling and make sure you are using the correct comparison operator.

Frequently Asked Questions

Can I edit the records shown in a query result?

Yes. Query results are live views of your table data, so you can edit, add, or delete records directly in the query results. Any changes you make update the underlying table immediately. However, some queries — particularly those that combine multiple tables or use certain functions — become read-only and cannot be edited.

What is the difference between a query and a filter?

A filter temporarily hides rows in a table without saving your settings. A query is saved as a separate object that you can run again. Queries are better for questions you ask repeatedly, while filters are better for one-time looks at your data.

Can a query pull data from tables in a different database?

Not directly in a simple query. You would need to link the external table to your current database first. Go to External Data, click New Data Source, and choose the file type. Once linked, the external table appears in your table list and you can use it in queries the same way as a local table.

How do I count records or sum values in a query?

Use an aggregate query. In Design View, click the Totals button (the sigma symbol) in the toolbar. A new row called Total appears in your grid. Click the Total cell under the field you want to count or sum and choose Count, Sum, Average, or another function. Access will return one row with the result instead of listing individual records.

What does it mean when a query returns no results?

Your criteria are too strict or do not match any records in your table. Check the spelling of text criteria, make sure dates are in the correct format, and verify that the field name is spelled correctly. Try removing one criterion at a time to see which one is causing the problem. You can also run the query without any criteria to confirm the table contains data.