One of the most powerful ways of analyzing your data in Access is by creating a Query.
Running a query is like asking your database a question. A query can retrieve data from
a single table, or from multiple tables, and it all depends on how complex your question
is. In this video, we're just going to focus on making a simple query that uses a single
table, and this is sometimes just called an advanced filter.
Let's suppose our bakery has an upcoming event, and I want to get a list of all of our customers
who live nearby so we can send them invitations. A query is going to be able to go into the
Customers table and find the names and addresses of the nearby customers.
To create a query, go to the Create tab, and then click the Query Design command. And then
you'll need to select the table or tables that you want to retrieve the data from. We're
just using the Customers table for now. Click Add, and then you can close this window.
When working with queries, there are a couple of different views that we're going to use.
If you click on the View drop-down arrow, you can see that we are in Design view. When
we finish the query, we're going to be viewing the results in Datasheet View. And you can
use this menu to switch views whenever you want.
The Customers table appears as a small window in the Object Relationship Pane, which is
this area here. And it has a list of all of the fields that are in this table, such as
First Name, Last Name, and Street Address. I'm going to resize it so I can see all of
What we're going to do is double-click each field that we want to include in the query.
We need the customer's name and address, so we'll double click on First Name, Last Name,
Street Address, City, State, and Zip Code. And we're not emailing or calling them, so
we don't need those fields.
Each field now appears in the area below, which is called the Design Grid.
Below the field names is the Table row, which shows which table each field comes from, and
this will become more important when we start dealing with multiple tables. And we're going
to modify some of these blank cells to refine our query.
We want the results to be sorted by last name, so in the sort row, we'll click the cell under
Last Name, and a drop-down arrow will appear. You can click it and then choose how you want
it to be sorted.
Next, we're going to use the Criteria row to filter the records so that it's only showing
the customers who live nearby. First of all, we want to send invitations to everyone who
lives in Raleigh, so we'll type Raleigh in the City column. And since we're looking for
an exact match, it will need to be in quotation marks.
So far, this query will show all of the customers who live in Raleigh, but none of the customers
in other cities. But one of the zip codes in Cary is close to Raleigh, and let's say
we'd like to send those customers an invitation as well. To do this, we're going to need to
add another criteria under zip code. Instead of using the Criteria row, we're going to
use the row below it, which is the 'or' row. We'll type 27513 in quotation marks. So now
the query will show customers who are in Raleigh *or* in zip code 27513.
The reason why we didn't put them on the same line, is that it would then find just the
customers who meet *both* criteria. In other words, the customer would need to live in
Raleigh *and* in zip code 27513. In some cases, you may want that, but in this example, it
will not give us the correct results.
Now we're finished designing this query, and the final step is to Run it. Click the Run
command in the Design tab, and the results will instantly appear in Datasheet View, which
looks exactly like a table. If you scroll through the results, you can see that each
customer lives either in Raleigh *or* in zip code 27513.
If you want to make any changes to the query, you can click the View drop-down arrow and
go back to Design View. And just like any object, it's a good idea to save it. I'll
call it Nearby Customers.
This was just a basic example, and in the next video we're going to talk about how to
make a query with multiple tables. You may want to practice this one a few times before
you go on, to make sure you're comfortable with the process.