Goodwill Community Foundation creating
opportunities for a better life
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 end 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 have just 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 grouped
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 it will add the values together and
there are some other options such as
average minimum maximum count and a few
more but in this case we want to use
some
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
sum of quantity 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 this 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