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 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 the fields.
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 both on the same line, is that the customers would
then need to 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.