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
your query. 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 of these
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 windows if you need to. Then we're going to double click the fields
that we need. I'm adding the customer's first name, last
name, street address, city, state, zip code, and phone number. And from the Orders table, we'll 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. 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. In the Phone Number column, 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. There are many other syntaxes that you can
use, and in the next video we'll look at a few other examples of these. In this case we are 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 needed them to meet one or the other,
then we would put one of the 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. [put boxes around city and area code] 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.
