You have already seen how an advanced filter is really a very basic query, run on 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 combine 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 expected.
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"?
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 a 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 expect.
To make any query work, you need to know three things.
What FIELDS do you want to see in the results?
In this step, you are selecting the fields you want Access to show you in your results.
Which TABLES or queries hold the info you need?
In this step, you're telling the 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.
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 want 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 in our results.
If we want to send coupons to our customers, we'd obviously want the customer's name and
address fields so the fields we will select are: 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 is 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 command on the Create tab on the Ribbon.
When the Show Table dialog box opens, pick all the tables that you outlined on your plan.
We need the Customers table, the Orders table and the Books table, so click on them to add
them.
Now I need to look at my plan and decide what fields I need from each table.
Well, I want to see the Customer's 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 the 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 a category of Technology, so in 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 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 look good.
By default whatever you drag to the bottom of your screen shows up in your results.
We really don't need to see the category so I'm going to show you how to remove this.
Go back to Design view and we're going to uncheck the box on the show row above technology.
That way the category won't show in our results.
Don't forget to save your query - we may want to run this query again the next time we have
a technology book released.
Save it as Customers of Tech 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.