We've already talked about how to create a simple query that only uses one table. But
queries that involve more than one table let you ask much more interesting questions to
your database. Now this also takes a little bit more planning than a single-table query,
and there are four steps that you can use to help you design it.
The first step is Pinpoint exactly what you want to find out. In other words, which question
are you trying to answer? In this example, let's say our bakery is sending out coupons
to our customers who live outside the city limits, to entice them to come back to our
bakery. Obviously, we don't want to send them to people who live really far away - we just
want to focus on people who live relatively close to Raleigh. And we're also just going
to send them to customers who have previously placed orders at our bakery.
The customers who meet all three requirements will receive coupons. So the question that
we're trying to answer is 'Which customers live in our area, are outside the city limits,
and have placed an order at our bakery?'
The second step is to Identify the information that we need. We'll need the customers' names
and their contact information. And in order to know whether they have placed an order
at our bakery, we'll also need to look at the Order ID numbers.
The third step is to Locate the tables that contain the information that we need. In this
case, the customers' names and contact information are stored in the Customers table. And the
Order ID numbers are stored in the Orders table. So that means we need both of these
tables in our query.
At this point, we have enough information to start creating our query. Go to the Create
tab, and select Query design. We're going to add the Customers table and the Orders
table. And then close this window. And you resize these if you need to. And we're going
to double click the fields that we need. So we'll add the customer's first name, last
name name, street address, city, state, zip code, and phone number. And from the Orders
table, we need the ID field.
When you have more than one table in a query, they will be connected by a line, and this
is called a Join. The join will often have an arrow that points to the left or the right,
which tells the query which table to look at first. Therefore, sometimes you'll need
to change the direction to get the results that you want. We're going to double-click
on the join to change it, and we want to select the third option which says 'Include ALL records
from the Orders Table'. And in the next video, we're going to talk a little more about why
we're choosing this option, but basically this means that it will pull from the Orders
table first, which ensures that only the customers who have placed an order will be included.
When you click OK, you can see that the arrow now points to the left.
The fourth step is to determine which search criteria you need to use. We're going to be
adding criteria under the City and Phone Number fields. First, we want to exclude all of the
customers who are in Raleigh. To do this, we're going to need to use a very specific
syntax. So for the City criteria, type Not In, and then in parentheses type Raleigh in
quotation marks. You can use this syntax whenever you want to exclude something from the query
results.
Now we also need some way of limiting the results to just the nearby towns. And in this
case we're going to do this by getting the area code from the Phone Number field. The
919 area code covers Raleigh and a number of nearby cities and towns, so this should
give us a pretty good range.
We'll need to use a syntax that looks at the beginning of each phone number. So type Like,
and in parentheses, type quote 919, asterisk, end quote. The asterisk means that any phone
number can come after the 919 area code.
And there are many other syntaxes that you can use, and in the next video we'll look
at a few other examples of these.
Now in this case we're putting the criteria on the same row, because we want the customers
to meet both of these criteria. If they just meet one of the criteria, then they're not
going to be included in the query results. If we want them to meet one or the other,
then we would one of these criteria on the next row.
This query is finished now, so we can Run it to see the results. And you can see that
each customer meets both criteria. They are not from Raleigh, and their phone numbers
begin with 919.
So generally, more complex queries require more planning. But you can make it a lot easier
by just following the four steps of Pinpointing exactly what you want to find out, Identifying
the information that you need, Locating the tables that contain the information, and Determining
exactly what criteria you need. And in the next video, we're going to look at joins and
search criteria in a little bit more detail.