in the last lesson we designed and built
a query using the query design command
now we'll 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 now this time
let's build a query to find out what
category of books we sell most often
start by thinking about what we want our
results to look like this will help us
know what we need we want to see the
title the author's first name and last
name the category and the price of our
best-selling books and maybe we 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's a good idea to include
everything you think you might want
while you're planning the query so we'll
include it in our plan next identify the
tables we'll need this time we'll just
need the orders table in 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
books ID number shows up most often
there so we 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 to now
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 mints hire new row the total
row it appears in the query design
window now by default this row sets
everything to group by something but all
we're really concerns with is the count
so that's what we're going to change now
and the total sell 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 groups each book's
title author price and category to show
up only one time in the results and it
shows us a number of times the book ID
appears in the orders table now if you
pay close attention you'll notice that
the last column says counts of book ID
but in design view
it just said book ID I just wanted to
point that out so it wouldn't be
confusing now we can 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 see filter for certain categories
if we wanted to know what technology
titles sell the best for example the
back in our query design view we add the
criteria of technology for the category
field this whole row is called carts
here
it filters your data based on the
criteria you set here now let's say this
query is our best sellers by category so
now you've seen how you can create and
modify queries and orders to answer the
questions you may have about your data
but you also saw that the data that is
returned doesn't look very nice if you
have to formally share them with someone
else our next lesson will show you how
to run reports and have a format them to
look good to other people