WEBVTT
Kind: captions
Language: en

00:00:11.480 --> 00:00:15.929
We're going to talk about a really neat type
of query called a totals query.

00:00:15.929 --> 00:00:19.270
Up until this point, the queries that we've
done have pulled in results from one or more

00:00:19.270 --> 00:00:22.410
tables, and listed them one by one.

00:00:22.410 --> 00:00:26.619
But a totals query does more than just list
the results; it combines them according to

00:00:26.619 --> 00:00:32.120
their value, and then performs a calculation
such as Sum, Average, or Count, so that you

00:00:32.120 --> 00:00:35.070
get much more useful information.

00:00:35.070 --> 00:00:40.320
I'm going to start by opening the Menu Items
Ordered query in our bakery database.

00:00:40.320 --> 00:00:43.950
This query uses several different tables to
give us a list of all of the items that have

00:00:43.950 --> 00:00:45.489
been ordered.

00:00:45.489 --> 00:00:49.329
If you look down the list you can see that
there's a lot of repetition, and that's because

00:00:49.329 --> 00:00:52.430
most of these items have been ordered more
than once.

00:00:52.430 --> 00:00:57.579
If we change this to a totals query, then
we'll just have one row for each item, and

00:00:57.579 --> 00:01:03.239
the values in the Quantity field will be added
up to show us what the total is for each item.

00:01:03.239 --> 00:01:13.670
First, you'll need to go to Design view…
and then click the Totals command… and the

00:01:13.670 --> 00:01:17.700
design grid now has a new row called the Total
row.

00:01:17.700 --> 00:01:22.470
And right now all of these say Group By, but
we're going to change the one in the Quantity

00:01:22.470 --> 00:01:24.150
field.

00:01:24.150 --> 00:01:28.399
So click the drop-down arrow… and we're
going to select Sum so that it will add the

00:01:28.399 --> 00:01:30.040
values together.

00:01:30.040 --> 00:01:36.840
And there are some other options such as Average,
Minimum, Maximum, and Count (and a few more),

00:01:36.840 --> 00:01:42.290
but in this case we want to use Sum.

00:01:42.290 --> 00:01:48.990
Now we can Run the query… and you can see
that there is one row for each product.

00:01:48.990 --> 00:01:56.360
And the Quantity field has changed to SumOfQuantity,
and this gives us the total for each item.

00:01:56.360 --> 00:02:01.500
That means we can tell at a glance that we've
sold 12 Christmas Cakes, 9 Carrot Cakes, and

00:02:01.500 --> 00:02:04.810
18 Cheesecakes.

00:02:04.810 --> 00:02:08.819
You may notice that some of these product
names appear on more than one row, and that's

00:02:08.819 --> 00:02:11.470
because they have more than one Sales Unit.

00:02:11.470 --> 00:02:16.000
For example, the Chocolate Chip cookies can
be sold by the Half-Dozen, Dozen, or as Single

00:02:16.000 --> 00:02:20.370
cookies, and these are really three different
products even though they have the same product

00:02:20.370 --> 00:02:22.430
name.

00:02:22.430 --> 00:02:27.060
So now that Access is calculating these totals
for us, we're able to get a much better idea

00:02:27.060 --> 00:02:29.169
of what people are buying from our bakery.