Access: Designing a Simple Query

Sharing buttons:

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

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 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.