Advanced Access queries using Totals!

It's time to try designing some advanced queries in Access! So far, the queries we've been writing (both in class and in Assignment 2), have ranged from basic (just listing certain data from a table) to intermediate queries where we restrict the data using conditions or parameters. For example, try creating the following queries in your new Police Fines database:

• Basic: List the driver, fine, location, reporting officer and date for all fines in the database.
• Intermediate: Limit the above query by only returning incidents that have occured since 1/4/05
• Something new: Modify your query so that it only returns incidents from the last 30 days (you will need to use the Now() function!)

So far so good? Our next step will be finding out how we can use the Totals function in our query design. You can access Totals by clicking on the little summation sign in the toolbar when designing your query. We'll have a bit of a demonstration of this in class, before having a go (together) at designing the following queries:

1. A count of the number of fines recorded in each location (qryFinesByLocation).
2. A count of the number of fines recorded by each officer (qryFinesByOfficer).
3. The maximum single fine (dollar value) reported by each officer (qryMaxFinesByOfficer)
   
4. A total of the fines reported by each officer (we always knew this was a money making scheme right ;-) (qrySumFinesByOfficer)
5. A total of the fines reported by each officer since 1/4/05 (qrySumFinesApril)
   
6. A total of the fines reported by each officer in the last 30 days (qrySumFines30days).
7. (V. Hard) A list of officers who haven't reported any fines since 1/4/05 (qryNoFinesApril).
8. A list of officers who haven't reported any fines in the last 30 days!

Phew!! If you can do those in Access, you're now an Access power user... you can pull useful information out of any database!