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
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
Access queries are very powerful, allowing you
to view, insert, delete, and calculate records and fields from numerous
tables in your database.
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:
- 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.
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"
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
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:
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
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