One of the primary advantages Microsoft Access provides over Microsoft Excel is the computational power of queries and relational reporting. Understanding how queries function, however, can be challenging. This guide introduces the Access Query Designer and provides practical tips to help you build reliable, high-performance queries.
What is a Query in MS Access?
Simply put, a query is a structured method to combine, filter, and summarize information stored in separate tables without duplicating data.
For example, imagine your database stores customer companies in one table and individual contacts in another. If you need to send a physical mailing to every contact using their parent company's mailing address, a query combines the contact's name with the customer's street address into a unified view. You eliminate data redundancy while keeping your underlying tables clean and normalized.
Access queries support full CRUD and analytical operations, allowing you to select, insert, update, delete, and calculate data across multiple tables simultaneously.
Building a Basic Query
To illustrate, we will build a basic query joining three related tables: Customers, Customer Types, and Orders.
- Open your Access database and select the Create tab on the Ribbon.
- Click Query Design in the Queries section.
Click to view full ribbon layout
When prompted, select each of the three tables and click Add (or double-click each table name), then click Close. If your relational integrity is configured in the Relationship window, Access automatically displays the join lines connecting your primary and foreign keys.
Configuring Relationship Lines:
- Joined fields must share identical data types (e.g., Number to Number).
- Relationships typically connect the Primary Key of one table to the Foreign Key of another.
- When nesting subqueries within a query, join lines must always be connected manually.
Click to enlarge table setup
Next, drag the desired fields from the table lists down into the query design grid at the bottom of the window:
Click to enlarge design grid
In our example, we selected Company Name, Customer Type, Order Date, Ship Date, and PO Number. Click the Run button (the red exclamation point on the ribbon) to execute the query and view the dataset:
Click to enlarge datasheet view
While the result looks like a standard Access table, it is a dynamic multi-table view. From this view, you can export your findings directly to Excel, PDF, or external SQL repositories.
Sorting and Filtering Data
To limit rows or order results logically, apply sorting instructions and criteria statements to individual columns in Query Design view:
Click to enlarge criteria example
Key Criteria Expressions & Rules:
- Multi-Field Sorting: Access applies sorting from left to right across the design grid. Sorting by CompanyName (Ascending) and then OrderDate (Descending) groups companies alphabetically, with each company's most recent orders displayed first.
- Wildcard Matching (Like Operator): Use asterisks to perform text matches:
- Like "Hello*" — Matches records beginning with "Hello".
- Like "*Hello" — Matches records ending with "Hello".
- Like "*Hello*" — Matches records containing "Hello" anywhere in the field.
- Exclusions: Use the not-equal operator <> "Residential" to exclude matching categories.
- Date Filtering: Wrap literals in octothorpes (hashes), such as < #1/1/2008#.
- AND vs. OR Logic: Criteria placed on the same row evaluate as AND (all conditions must match). Criteria placed on the secondary or: row evaluate independently.
- Hiding Columns: Uncheck the Show checkbox if you need a field for sorting or criteria validation without displaying it in the resulting datasheet.
Editing Data in a Query
Depending on query structure, you can frequently edit data directly in the datasheet view. However, complex multi-table joins may trigger warnings like "Record Locked" or "Cannot Add Record" if Access cannot determine which underlying table row to update.
Be aware that in relational designs, editing a lookup text value in a joined parent table updates that value for all associated records throughout the database:
Click to enlarge lookup view
Why Are Records Missing in My Access Query?
If an executed query returns zero rows or fewer records than expected, the issue is typically overly restrictive criteria, missing join fields, or strict Inner Join settings.
By default, Access creates an Inner Join, showing only records where key values match in both tables. If a Customer has not yet been assigned a CustomerTypeID, that entire customer row is omitted from the output.
The Solution: Double-click the join line connecting the tables in Query Design view to open Join Properties. Select Option 2 or 3 (an Outer Join) to include all records from your primary table regardless of whether matching records exist in the related lookup table.
Click to enlarge Join Properties window