Access Query Design
How to Use Access Queries to Summarize and Analyze your Access Data
Table of Contents
What is a Query in MS Access
Building a Basic Query
Sorting and Filtering
Data Editing Data in
a Query
Why Are
Records Missing in My Access Query!
Special Types of Queries
Calculating Data in a Query
Grouping Data with Access Aggregate Queries
Find Matching Records in Access Tables
Finding Duplicate
Records
Find
Missing Records in Access Tables
One of the primary benefits of Microsoft Access over Excel is the
power of Access queries and reporting. Understanding Access
queries, however, isn't always easy. This article gives you
an introduction to the Access query designer, and tips for creating
advanced Access queries to save you hours of time.
Bookmark this page
Access queries are very powerful, allowing you to view, insert, delete, and calculate records and fields from numerous tables in your database.
Bookmark this page
What is a Query in MS Access?
Simply put, a query is a way to combine information stored in separate tables to avoid duplication in the tables themselves. For example, you have a table for customers, and a table for contacts. But you need to send a mailing to each contact using the customer's address. You can use a query to show each contact, the customer name, and the mailing address of the customer. Data from separate (but related) tables, without needing to duplicate anything in the contacts table.Access queries are very powerful, allowing you to view, insert, delete, and calculate records and fields from numerous tables in your database.
Building a Basic Query
We'll start by building a basic Access query joining three tables. Say your database contains a table for Customers, Customer Types, and Orders. Start by selecting the "Create" Ribbon tab. Select "Query Design"
You will be prompted to select the tables and/or queries to include in your query. Select each table and click "Add" or double-click them. Remember to click "Close" when you are done adding your tables/queries. If your database relationships have been defined correctly, Access will automatically draw the relationship lines between your tables.
If you don't see relationship lines between your tables
when you add them, you will want to draw them manually. To do so, you'll
need to select one field, and drag it to the related field in the
other table. Without that step, Access will not understand
how to link the records together between the tables. A few
key things to remember when drawing the relationship lines:
Now you can begin dragging fields from each table/query into the field section of the query design window.
- The two related fields need to have the same data type (e.g., Text or Numeric)
- Typically field will be the primary key of the table, and the other will be a corresponding foreign key.
- If you use a query within your query, you will always need to define the relationships to any other table/query manually.
Now you can begin dragging fields from each table/query into the field section of the query design window.
As shown above, we've added the Company Name, Customer Type, Order Date, Ship Date, and PO number from three separate tables into one view. Next click the "Run" icon on the ribbon (the red exclamation mark) and we see the Query View:
The view above looks much like a standard Access table, when in fact it is a consolidation of several tables. In this view, you can export your data to Microsoft Excel, PDF, or other formats.
Sorting and Filtering Data
Once you have a basic query in place, you'll want to be able to filter your records to perhaps display only certain records that match your criteria, or sort by one or more fields.
In the screen above, we have sorted the records by CompanyName (Ascending
means A-Z) and by OrderDate (Descending means newest dates first).
Note that the CompanyName field is listed first from the left, so the
list will be sorted by Company Name, then if there are multiple records
for a company, the newest order will be shown first. In the Criteria
section, we are filtering to shown any record where the Company Name
contains "Research." the "Like" statement
is very powerful, and lets you find records that begin with a word (Like "Hello*"),
end with a word (Like "*Hello"), or contain a word (Like "*Hello*").
All based on where you place the asterisk. Note that CompanyName
is a text field, so the criteria needs to be contain in quotes.
Next, we're going to exclude any records where the CustomerType is "Residential"
using the Not Equal to (<>) sign. We also want only records
where the Order Date is less than 1/1/2008. Note that the pound
sign (#) is used to contain dates in Access.
Lastly, notice that the second line of the Criteria section starts with "or:" and we've entered a criteria. You can use the second line to contain separate criteria sets. In this case, we want either any Company with "Research" in the name that is not a Residential customer type and has orders before 1/1/2008 OR any record where the Ship Date is empty (that is what "Is Null" means).
If you have fields in your query you want to filter or sort by, but you don't want to see them in the actual results view, simply uncheck the "Show" checkbox for that field. Access will still use it as criteria for sorting/filtering, but hide it in the Query View.
Lastly, notice that the second line of the Criteria section starts with "or:" and we've entered a criteria. You can use the second line to contain separate criteria sets. In this case, we want either any Company with "Research" in the name that is not a Residential customer type and has orders before 1/1/2008 OR any record where the Ship Date is empty (that is what "Is Null" means).
If you have fields in your query you want to filter or sort by, but you don't want to see them in the actual results view, simply uncheck the "Show" checkbox for that field. Access will still use it as criteria for sorting/filtering, but hide it in the Query View.
Editing Data in a Query
Depending upon how complicated your Access query is, you may be able to edit data. But don't be alarmed if you receive a message "Record Locked" or "Cannot Add Record." When you join multiple tables, Access may need extra guidance for how to handle edits or a new record. And remember that with a relational database, editing a single row in a query can actually change the value for all records. Take for example our field "CustomerType." Changing the value in one row will actually change the text value in all records since we are modifying the field in the table tblCustomerType, not the underlying identifier field in the Customer table:
Why Are Records Missing in My Access Query!
You may be alarmed to find that you've created a query that returns no records, or many fewer than you expect! Either your criteria is too strict, you've joined tables incorrectly, or, you need to define the relationships between the fields more precisely. Taking our example query, recall that we joined the field CustomerTypeID and fk_CustomerTypeID in the Customer Type and Customer tables, respectively. What if you have records in the Customer table that haven't been assigned a Customer Type value? They will be missing from your query! That is because Access, by default, assumes you want to see only records from two joined tables where the values match. An empty value on a numeric AutoNumber field won't match! Fortunately the remedy is quite simple. You'll want to double-click the relationship line and explicitly tell Access you want to see all records from the Customer table even if there is no matching record in the Customer Type table: