you've already seen how an advanced
filter is really a very basic query run
and only one table in your database
well sometimes you are going to want to
be able to pull out information from
more than one table at a time and
combined it in a meaningful way so that
you can analyze it that is what a query
can do for you it pulls out the data you
tell it to from the tables you tell it
to look in and then shows it to you in a
way that is easy for you to analyze but
you have to be very careful in the way
you tell it to get your information or
it may not return the results you expect
it let's talk about how to design a
query before we even get into using
access to set it up queries help you
answer questions you may have about your
data like which customers have ordered
technology books now before we go much
further I think it will help if we talk
about some query design basics you don't
need to know how to write in the formal
query language to use access but
understanding how queries work and using
a process to design your own query can
certainly make the process easier and
increases the likelihood that you'll get
the results you'll expect to make any
query work you need to know three things
what fields do you want to see in the
results now in this step you're
selecting the field you want access to
show you in your results which tables or
queries hold the info you need in this
step you're telling access where to get
the information from what conditions do
you want the data to meet this is
telling access that you want to see all
the records where a certain condition is
met now let's plan this out on paper for
our first question which customers have
ordered technology books we have a new
technology series and we wanted to send
these customers a coupon for the next
book release so let's start planning
first we need to select the fields we
want to see and
results if we want to send coupons to
our customers we'd obviously want the
customers name and address fields so the
fields we will select our first name
last name street city state and zip
these are in the customers table now
which tables hold these fields that we
need we already know that we need the
customers table for the names and
addresses but we're also going to need
the books table to tell us which books
are technology books and we'll need the
orders table because that's where a book
is joined with a customer now what
conditions do we want the data to meet
well we want to see only the customer
names and addresses for customers who
have ordered books that have a category
of Technology now that we have a plan we
can start building a query in Access we
are going to build this query from
scratch you do that with the query
design commands on the create tab on the
ribbon now when the show table dialog
box opens pick all the tables that you
outlines on your plan we need the
customers table the orders table in the
books table
so click them to add them now I need to
look at my plan and decide what feels I
need from each table well I want to see
the customers first and last name and
their street address city state and zip
so I'll highlight those fields and drag
them down to the bottom portion of the
screen I also need a category field from
the books table so I'll drag that down
there too these are all the fields I
care about so now I have to tell access
how to show them to me I want only books
with the category of technology certain
criteria I'll type technology access
puts the quotes around it because that
is the exact word it is looking for in
the books table now all you have to do
is hit and run in the results tab to run
the query
if your results don't look like you
expect them to you may not have included
everything from your plan but ours looks
pretty now by default
whatever you drag to the bottom of the
screen shows up in your results we
really don't need to see the category so
I'm gonna show you how to remove this
let's go back to design view and we're
gonna uncheck the box on the show row
above technology that way the category
won't show on our results now don't
forget to save your query we may want to
run this query again the next time we
have a technology bullet release save it
as customers of tip books or something
else logical okay so now you've seen how
to plan your query out on paper first
and how to use the query design tool to
set up a simple select query next we'll
look at how to use the query wizard to
find out which book is our bestseller