Get quick answers with a crosstab query in Access
Create a text box from existing text
People often use text boxes to highlight a specific part of their document. But when you want to use a text box, there's no need to retype the text.
Follow these steps:
- Copy the text you want to include in the text box to the location where you want to insert the text box.
- Select the copied text and go to Insert | Text Box.
The selected text retains the format of the original. As with any text box, you can reformat the text by selecting it, right-clicking the selection, and choosing Font.
Microsoft Excel
Compute an average that excludes zero values
While Excel's AVERAGE function ignores blank cells, it doesn't ignore cells that contain 0. This can result in inaccurate analysis of the data.
For example, let's say you want to calculate the average score for all students who took the final exam. Using the AVERAGE function, you might enter: =AVERAGE(B3:B23).
But if two students were absent and received a grade of 0, the result of the formula wouldn't give a true picture of the average grade. However, you can calculate the average and exclude the absentee students' grades.
Follow these steps:
- Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))
- Press [Ctrl][Shift][Enter].
Microsoft Access
Get quick answers with a crosstab query
When you need quick answers to everyday business questions, let Access' Crosstab Query Wizard help. For 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.
Follow these steps:
- In the database window, click Queries, and click New.
- Choose Crosstab Query Wizard, and click OK.
- Choose the Hours Worked table, and click Next.
- Select EmployeeID for the field values you want for row headings, and click Next.
- Select Week Ending for the field values you want for column headings, and click Next.
- Select Date to coincide with the date value in the Week Ending field, and click Next.
- Select Hours from the Fields list box, choose Avg from the Functions list box, and click Next.
- Name the query, and click Finish.
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.