Creating Aggregate Functions in Access

Tutorial

The Tutorial #3 in the New Perspectives Access 2000 book has misleading discussion of aggregate functions. In particular, the query will not run until all three aggregate functions have been entered nor will it run as stated if there are any other fields present in the query. The following is an attempt at a clarified version of the tutorial.

Aggregate functions are operations on fields that calculate a single result based on multiple records. A view that displays an aggregate function will, in general, have fewer rows (records) than one that does not display an aggregate function because the aggregate function has taken multiple records and processed them to create a single record with the final result.

Examples of aggregate functions are

• The sum of all of a field's values in a table/query.
• The sum of all of a field's values for each unique value of another field.
• The average of all of a field's values in a table/query.
• The number of records in a table/query.

Making a query that shows the sum of all the invoice amounts:

1. Open up your Restaurant database, or get the partially complete database (right-click and save as: Restaurant2.mdb)
2. Create a new query using the Design View, saving it under the name, "Statistics".
3. Add both the Customer and Order table to the query builder.
4. Drag the InvoiceAmt field down to the design grid.
5. Click the "Totals" button on the top toolbar (it has a capital Greek "sigma" character on it). You should see a new row appear in the design grid, called "Total:". The Totals row should say "Group By" in it.
6. Run the query. You should see a table with many rows of numbers in it.
7. Now pull the drop-list down by clicking the right side of the Totals text box and select "Sum".
8. Re-run the query. You should see only one row with the total of all the invoice amounts.

Here are some things to try:

Sum of all the paid and unpaid invoices:

1. To the above query, add the Paid field.
2. Run the query. You should see two rows, one with Paid checked and one with it unchecked. The checked Paid row has the sum of all the paid invoices. The unchecked Paid row has the sum of all the unpaid invoices.

Sum of all the unpaid invoices:

1. To the previous query above, add the criteria "=false" to the Paid field in the Design grid.
2. Run the query. You should see only one row now with the sum of all the unpaid queries.

Sum of all the unpaid invoices for each customer:

1. Add a Customer field to the previous query above.
2. Run the query. You should see multiple rows, each with a unique customer and the sum of all that customer's unpaid invoices.

Sum of all the unpaid invoices for each customer and the count of how many unpaid invoices there were for that customer:

1. Add another InvoiceAmt field to the Design grid.
2. Set that field's Totals text box to be "Count".
3. Run the query. You should see the same output as the previous query, but with an additional field that tells you how many unpaid invoices that customer has (that were summed together).

The database at this point can be found here: right-click and save as: Restaurant3.mdb.