Access 2010

Planning our query

Let's go through this planning process with a query we'll run on our bakery database. As you read through the planning process step by step, think about how each part of the planning process could apply to other queries you might run.

Pinpointing the question we want to ask

Our bakery database contains many customers, some of whom have never placed an order but who are in our database because they signed up for our mailing list. Most of them live within the city limits, but others live out of town or even out of state! We want to get our out-of-town customers who've placed orders in the past to come back and give us another try, so we're going to mail them some coupons. We don't actually want our list to include customers who live too far away—sending a coupon to someone who doesn't live in our area probably won't make them come in. So really, we just want to find people who don't live in our city but who still live in our area.

Identifying the data we want the query to find

In short, the question we want our query to answer is this: Which customers live in our area, are outside the city limits, and have placed an order at our bakery?

Identifying the information we need

What information might we want to see about these customers? Obviously, we'll need the customers' names and their contact information—their addresses, phone numbers, and email addresses. But how are we going to know if they've placed orders? Each record of an order identifies the customer who placed that order. If we include the order ID numbers, we should be able to narrow our list down to only customers who have previously placed orders.

Making a list of the information we want the query to find

Locating the tables containing the information we need

In order to write a query, you need to be pretty familiar with the different tables in your database. From working extensively with our own database, we know the customer information we need is located in fields in the Customers table. Our Order ID numbers are in a field in the Orders table. We only need to include these two tables to find all of the information we need.

Making a list of the tables where we can find the information we need for our query

Determining the criteria our query should search for

When you set criteria for a field in a query, you are basically applying a filter to it that tells the query to retrieve only information that matches your criteria. Review the list of fields we are including in this query. How and where can we set criteria that will best help us answer our question?

We don't want customers who live in our town, Raleigh, so we want a criteria that will return all records except those with "Raleigh" in the city field. We don't want customers who live too far away, either. All phone numbers in the area start with the area code "919", so we'll also include a criteria that will only return records whose entries from the phone number field begin with "919". This should guarantee that we'll only send coupons to customers who live close enough to actually come back and use them.

We won't set a criteria for the order ID field or any other fields, since we want to see all of the orders made by people who meet the two criteria we just set.

Figuring out the criteria we will use to build our query

To write queries, you'll need to be able to set criteria in a language that Access understands. As you can see in the image above, our criteria requiring phone numbers to begin with "919" must be typed like this: Like ("919*"). To learn how to write additional criteria, consult our printable Query Criteria Quick Reference Guide in the Extras section of this tutorial. The guide includes several of the most commonly used criteria in Access queries.