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 and 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 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 a 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