Goodwill Community Foundation creating
opportunities for a better life
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 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've 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
you can resize these if you need to then
we're going to double click on the
fields that we need so we'll add the
customers 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
joined 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 goes from right to 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 and you can use the 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
nine one nine area code covers Raleigh
and a number of nearby cities and towns
so this should give us a pretty good
range will need to use a syntax that
looks at the beginning of each phone
number so type like and in parentheses
type quote nine one nine asterisk
end quote the asterisk means that any
phone number can come after the nine one
nine 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 needed them to meet one or
the other then we would put 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 nine one nine 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
a little bit more detail