Microsoft Access Aggregate Query
Build a Query with SUMs, COUNTs, AVERAGEs, and more.
Q: I would like to be able to take my customers and add
up total sales for each of them. I know how to do it in a report footer,
but how can I group my sales totals by customer in a query so I can use
that figure for other things.
A: To do this, use an
Aggregate Query... also called a Summary Query or Totals Query.
There is a little button on the query toolbar that looks like a wierd
"E" (actually the Greek letter Sigma). Press that and you get a new
Total row. Just add the fields you want to group by (like customer
name or ID) and the fields you want to run calculations on (like SUM or
Average) and there you go.
Here's a screen shot from one of my tutorials showing the Total row.
Here is a FREE VIDEO TUTORIAL
that will walk you through this topic in detail.
I teach the concepts in this free mini tutorial in my
Access 221 course. These are
full courses - over 60 minutes in length each. I spend a lot more time going
over how to do this, in addition to lots more tips and tricks.
| 
