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 and a little bit more
detail
you