Getting a different look with crosstab queries

Queries generally follow the rows and columns or records and fields model for their layout. There's also a slightly modified layout option called the Crosstab query, that gives us headers along the top and side and aggregate or summary data appears in the middle. Let's put one together using our phone list so you can see how this works. Now right now our EmployeePhone lists are stored in a second related table. This provides flexibility in adding multiple numbers for each employee where most employees will have at least one office phone, but some might have additional lines for their cell phone, their fax number or their home phone or an emergency contact number.

I would like to first put together a Select query that gathers all of these numbers and then convert that query into a Crosstab format, so you can see the improvement in legibility. I'll go ahead and close the EmployeePhone table and we'll start a new query in Design view. I'll add the Employees table and the EmployeePhone table and close the Show Table window. Now I'll pull in a few fields. From the Employees table I'll double-click on FirstName and LastName and from the EmployeePhone phone table I'll grab the Number and the Type.

Let's go ahead and view the datasheet to see the records we've collected. You'll see multiple lines for each employee which is not quite ideal. Let's go back into Design view and clean it up. I'm going to change the Query Type from Select query over here to Crosstab query. When I do that I get two additional rows down here in the grid, Crosstab and Total. In the Crosstab row we're going to define what fields go where in the grid. Our first and last names are going to be Row Headings. I'll click the dropdown and choose the Row Heading.

The Type will become our Column Heading and the number is what's going to appear in the middle, we're going to choose Value. Now also for the number we need to choose how we want to show those values. We're going to change this Totals row to First. The Total is how we want to aggregate or summarize the data in this area. Most of these options will make sense for numerical data such as sum or average but wouldn't make any sense for phone numbers. We'll choose First here, so that we're not applying any sort of mathematical transformations to the data. Now finally I want to make this a usable directory so I'm going to apply a Sort on LastName to alphabetize it ascending.

Now let's go ahead and view the datasheet to see the difference. It's exactly the same data as before just displayed in a more compact way. Each employee now only has one row but we still see all of their phone numbers with a type listed across the top. Further, this layout makes it very easy to see what employees are missing crucial information. Maybe it's time to put some pressure on people to give us those emergency contact numbers. Crosstab queries can provide a different organization to your data, sometimes making it easier to read, make comparisons or spot trends.

The aggregate values that appear at the intersection of the row and column can also be set to display account of how many records are in that group or their sum total, average, lowest, or highest values.