We're going to talk about a really neat type of query called a totals query.
Up until this point, the queries that we've done have pulled in results from one or more
tables, and listed them one by one.
But a totals query does more than just list the results; it combines them according to
their value, and then performs a calculation such as Sum, Average, or Count, so that you
get much more useful information.
I'm going to start by opening the Menu Items Ordered query in our bakery database.
This query uses several different tables to give us a list of all of the items that have
been ordered.
If you look down the list you can see that there's a lot of repetition, and that's because
most of these items have been ordered more than once.
If we change this to a totals query, then we'll just have one row for each item, and
the values in the Quantity field will be added up to show us what the total is for each item.
First, you'll need to go to Design view… and then click the Totals command… and the
design grid now has a new row called the Total row.
And right now all of these say Group By, but we're going to change the one in the Quantity
field.
So click the drop-down arrow… and we're going to select Sum so that it will add the
values together.
And there are some other options such as Average, Minimum, Maximum, and Count (and a few more),
but in this case we want to use Sum.
Now we can Run the query… and you can see that there is one row for each product.
And the Quantity field has changed to SumOfQuantity, and this gives us the total for each item.
That means we can tell at a glance that we've sold 12 Christmas Cakes, 9 Carrot Cakes, and
18 Cheesecakes.
You may notice that some of these product names appear on more than one row, and that's
because they have more than one Sales Unit.
For example, the Chocolate Chip cookies can be sold by the Half-Dozen, Dozen, or as Single
cookies, and these are really three different products even though they have the same product
name.
So now that Access is calculating these totals for us, we're able to get a much better idea
of what people are buying from our bakery.