[Music]
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 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
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 like what you just saw
radix add a comment subscribe to our
Channel
the freedom to learn
what you want when you want absolutely
free
GCF learn free.org