Use Access' Where clause to calculate query totals for specific conditions

by Mary Ann Richardson | Jun 05, 2007 7:00:00 AM

Tags: Microsoft Office, Databases, Mary Ann Richardson, Microsoft Access, cell, Microsoft Certified, Click Create Query, Total Cell, Microsoft Corp., Microsoft Office Suite Access Tips Newsletter

Takeaway: Access is versatile enough for you to sort parameter query totals that are unique to certain conditions. Mary Ann Richardson shows how to use the Where clause to make calculations based on specific criteria within your data.

In addition to letting you total, average, count, and perform other calculations on entire columns of data, the Total cell in Access' Query Design window allows you to perform those same calculations on only those records where the data meet certain conditions. For example, suppose you would like to know how many Microsoft-certified employees were hired from January 1, 2001 to the present. To create a parameter query that can answer this question, follow these steps:

1. Open the Employees database.
2. Click Queries under Objects in the Database window.
3. Click Create Query in Design View.
4. Select the Employees Records table, then click Add, and then click Close.
5. Double-click EmployeeID in the Field list.
6. Right-click the Sort cell under the EmployeeID Field cell and select Totals.
7. Click the drop-down arrow of the Total cell and select Count.
8. Double-click Certified in the field list.
9. Clear the check mark from the Show cell.
10. Enter Microsoft in the Criteria cell for the Certified field.
11. Double-click HireDate in the field list.
12. Click the drop-down arrow of the Total cell for HireDate and scroll to and select Where.
13. Clear the check mark from the Show cell.
14. In the Criteria cell for the Hire Date field, enter: >[Enter Hire Date].
15. Close and save the query.

When you run the query, you will get a message box that will let you enter the hire date from which you want Access to total the number of Microsoft-certified employees hired. For example, if you enter 1/1/2001, Access will count all the records that have dates later than 1/1/2001 in the Hire Date field and return the total amount of records that satisfy that condition in the query results.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.