Get quick answers with a crosstab query in Access

Microsoft Word

---

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:

1. Copy the text you want to include in the text box to the location where you want to insert the text box.
2. Select the copied text and go to Insert | Text Box.

Word inserts a text box displayed with a crosshatch border and eight handles around the selected text, so you can resize or move it within the document. Any remaining document text automatically wraps around it.

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:

1. Enter the following formula: =AVERAGE(IF(B3:B23<>0,B3:B23))
2. Press [Ctrl][Shift][Enter].

The entered formula first creates an array that includes only nonzero values in the range. The AVERAGE function then uses this array as its argument.

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:

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.