In the last lesson, we designed and built a query using the Query Design command.
Now we will build another query, but this time we'll add an extra function that helps
summarize our results.
Then, we'll sort and filter our query results, to define them even more .
Okay, this time, let's build a query to find out what category of books we sell most often.
Start by thinking again about what we want our results to look like.This will help us
know what we need.
We want to see the title, authors first name and last name,the category, and the price
of our best selling books, and maybe we'd even want to see the number of times each
book was ordered.
If we decide we don't want this information once we've run the query, we can always go
back and delete it.
But it is a good idea to include everything you think you might want while you are planning
the query, so we'll include it in our plan now.
Next, identify the TABLES we'll need this time we'll just need the Orders table and
the Books table.
Finally, think about the conditions we need our data to meet.We will want to see ONLY
the books that show up in the Orders table and we want to know which book's ID number
shows up most often there so we will need to COUNT how often each book ID appears in
the Orders table.
All right, we have our plan, so let's get started.
From the Create tab, select the Query Design command.
Pick which tables or queries you want to use as a basis for the new query.
We need the Books table, and the Orders table so pick those from the list.
From the Books table we want everything except the Book ID.
Now we need the Book ID from the Orders table so we'll drag that down too.
This makes sure we have a list of all the books we ordered.
Here is where our Count comes in.
If we click run right now we see one record for each time a book was ordered.
Well, Access can group the records with the same Book ID, and give us a count for each
one.
To do that, let's go back to design view and click the Totals command.
This makes an entire new row; the total row and it appears in the Query Design window.
By default this row sets everything to Group By something.
But all we're really concerned with is the count.
So that's what we're going to change.
Now in the Total cell for Book ID click on the drop down and select Count.
This is how we tell Access to count the number of times each Book ID appears in the Orders
table.
Now when we click Run!, we get a column that tells us how many times each book has appeared
in the Orders table.
It has grouped each book's title, author, price and category to show up only one time
in the results, and it shows us the number of times the Book ID appears in the orders
table.
If you pay attention you'll notice that the last column says "count of book ID" and before
in Design view it just said Book ID.
I just wanted to point that out so it wouldn't be confusing.
We can now go back and change the design of the query to sort the count highest to lowest,
or descending, so we can see which books were ordered most at the top.
We could also change the design to Filter for certain categories if we wanted to know
what Technology titles sell the best, for example.
Back in our query Design View, we'd add the Criteria of Technology for the Category field.
This whole row is called Criteria.
It filters your data based on the cirteria you set here.
Now let's save this query as our Bestsellers by Category.
So now you've seen how you can create and modify queries in order to answer the questions
you may have about your data.
But you also saw doesn't look very nice if you had to formally share them with someone
else.
Our next lesson will show you how to run reports, and how to format them to look good to other
people.