When you need quick answers to everyday business questions, let Access' Crosstab Query Wizard help...
Example, say you need to know the average number of hours each employee works per week. To find the answer, you need to analyze the data in the Hours Worked table, which contains three fields: EmployeeID, Week Ending, and Hours, and this is how we do it:
1. In the database window, click Queries, and click New.
2. Choose Crosstab Query Wizard, and click OK.
3. Choose the Hours Worked table, and click Next.
4. Select EmployeeID for the field values you want for row headings, and click Next.
5. Select Week Ending for the field values you want for column headings, and click Next.
6. Select Date to coincide with the date value in the Week Ending field, and click Next.
7. Select Hours from the Fields list box, choose Avg from the Functions list box, and click Next.
8. Name the query, and click Finish.
Included in the results of the crosstab query is a column that displays the average number of hours worked by each employee per week.
The Crosstab Query Wizard works with only one table at a time. If you need to create a query with fields from more than one table, first create a select query that includes the necessary fields, and specify that query in step 3.