WEBVTT
Kind: captions
Language: en

00:00:07.370 --> 00:00:12.730
In the last lesson, we designed and built
a query using the Query Design command.

00:00:12.730 --> 00:00:17.590
Now we will build another query, but this
time we'll add an extra function that helps

00:00:17.590 --> 00:00:19.300
summarize our results.

00:00:19.300 --> 00:00:23.590
Then, we'll sort and filter our query results,
to define them even more .

00:00:23.590 --> 00:00:31.279
Okay, this time, let's build a query to find
out what category of books we sell most often.

00:00:31.279 --> 00:00:35.530
Start by thinking again about what we want
our results to look like.This will help us

00:00:35.530 --> 00:00:37.630
know what we need.

00:00:37.630 --> 00:00:43.829
We want to see the title, authors first name
and last name,the category, and the price

00:00:43.829 --> 00:00:49.589
of our best selling books, and maybe we'd
even want to see the number of times each

00:00:49.589 --> 00:00:52.820
book was ordered.

00:00:52.820 --> 00:00:57.460
If we decide we don't want this information
once we've run the query, we can always go

00:00:57.460 --> 00:00:59.320
back and delete it.

00:00:59.320 --> 00:01:04.220
But it is a good idea to include everything
you think you might want while you are planning

00:01:04.220 --> 00:01:07.730
the query, so we'll include it in our plan
now.

00:01:07.730 --> 00:01:13.040
Next, identify the TABLES we'll need this
time we'll just need the Orders table and

00:01:13.040 --> 00:01:14.440
the Books table.

00:01:14.440 --> 00:01:19.860
Finally, think about the conditions we need
our data to meet.We will want to see ONLY

00:01:19.860 --> 00:01:25.640
the books that show up in the Orders table
and we want to know which book's ID number

00:01:25.640 --> 00:01:31.720
shows up most often there so we will need
to COUNT how often each book ID appears in

00:01:31.720 --> 00:01:32.820
the Orders table.

00:01:32.820 --> 00:01:37.630
All right, we have our plan, so let's get
started.

00:01:37.630 --> 00:01:43.630
From the Create tab, select the Query Design
command.

00:01:43.630 --> 00:01:47.930
Pick which tables or queries you want to use
as a basis for the new query.

00:01:47.930 --> 00:01:52.700
We need the Books table, and the Orders table
so pick those from the list.

00:01:52.700 --> 00:02:04.030
From the Books table we want everything except
the Book ID.

00:02:04.030 --> 00:02:08.899
Now we need the Book ID from the Orders table
so we'll drag that down too.

00:02:08.899 --> 00:02:14.069
This makes sure we have a list of all the
books we ordered.

00:02:14.069 --> 00:02:16.200
Here is where our Count comes in.

00:02:16.200 --> 00:02:24.969
If we click run right now we see one record
for each time a book was ordered.

00:02:24.969 --> 00:02:30.840
Well, Access can group the records with the
same Book ID, and give us a count for each

00:02:30.840 --> 00:02:31.840
one.

00:02:31.840 --> 00:02:39.129
To do that, let's go back to design view and
click the Totals command.

00:02:39.129 --> 00:02:44.599
This makes an entire new row; the total row
and it appears in the Query Design window.

00:02:44.599 --> 00:02:49.639
By default this row sets everything to Group
By something.

00:02:49.639 --> 00:02:53.870
But all we're really concerned with is the
count.

00:02:53.870 --> 00:02:57.069
So that's what we're going to change.

00:02:57.069 --> 00:03:04.120
Now in the Total cell for Book ID click on
the drop down and select Count.

00:03:04.120 --> 00:03:09.390
This is how we tell Access to count the number
of times each Book ID appears in the Orders

00:03:09.390 --> 00:03:10.870
table.

00:03:10.870 --> 00:03:17.389
Now when we click Run!, we get a column that
tells us how many times each book has appeared

00:03:17.389 --> 00:03:19.160
in the Orders table.

00:03:19.160 --> 00:03:24.550
It has grouped each book's title, author,
price and category to show up only one time

00:03:24.550 --> 00:03:28.989
in the results, and it shows us the number
of times the Book ID appears in the orders

00:03:28.989 --> 00:03:31.529
table.

00:03:31.529 --> 00:03:36.370
If you pay attention you'll notice that the
last column says "count of book ID" and before

00:03:36.370 --> 00:03:39.249
in Design view it just said Book ID.

00:03:39.249 --> 00:03:46.469
I just wanted to point that out so it wouldn't
be confusing.

00:03:46.469 --> 00:03:51.900
We can now go back and change the design of
the query to sort the count highest to lowest,

00:03:51.900 --> 00:03:57.370
or descending, so we can see which books were
ordered most at the top.

00:03:57.370 --> 00:04:03.389
We could also change the design to Filter
for certain categories if we wanted to know

00:04:03.389 --> 00:04:06.879
what Technology titles sell the best, for
example.

00:04:06.879 --> 00:04:14.790
Back in our query Design View, we'd add the
Criteria of Technology for the Category field.

00:04:14.790 --> 00:04:16.850
This whole row is called Criteria.

00:04:16.850 --> 00:04:23.340
It filters your data based on the cirteria
you set here.

00:04:23.340 --> 00:04:27.169
Now let's save this query as our Bestsellers
by Category.

00:04:27.169 --> 00:04:32.020
So now you've seen how you can create and
modify queries in order to answer the questions

00:04:32.020 --> 00:04:34.139
you may have about your data.

00:04:34.139 --> 00:04:40.490
But you also saw doesn't look very nice if
you had to formally share them with someone

00:04:40.490 --> 00:04:41.520
else.

00:04:41.520 --> 00:04:46.680
Our next lesson will show you how to run reports,
and how to format them to look good to other

00:04:46.680 --> 00:04:46.850
people.