Crosstab Query

A “Crosstab query” restructures the result of the query by them grouping together horizontally and vertically, so that they are easier to read. You can compare it to an “Excel” chart. It also calculates the sum, average or other functions, and groups them into two sets of values. There are both pros and cons to this type of data sheet.

A “Crosstab Query” can be done in two ways, first by clicking on the “Query Design” button in the “Ribbon” and then changing it into a “Crosstab Query”. A second way is to use the “Query Wizard”.

In this example, I use the “Query Wizard”.

Select the “Create” tab in the “Ribbon” and click the “Query Wizard”.

In the “New Query”, select the second option which is the “Crosstab Query Wizard” and click the OK button.

This opens the “Crosstab Query” wizard” dialog. In the window of the dialog box, choose the table or query or both, from which the information must come. In this example, I select the “Total Item” query and click the “Next” button

In the second wizard window, I determine which fields I want to use as row headers. I select the “Item” in the left part and click the right pointing arrow. This places the field in the right part of the dialog.

Click the “Next” button.

In the next window, we determine our column headings. I choose the “Date” field and click the “Next” button.

Since the wizard recognizes this is as a field with data type “Date / Time”, it asks a number of different time intervals on which the information can be grouped. I choose “Month” and click the “Next” button.

In the left part of the next window, we determine the field that must be calculated. I select “Price” and from the field list on the right side, I select “Sum” and click “Next”.

In the last window of the wizard, you give it a name. I leave the “Query Design View” checkbox checked and click the “Finish” button.

This gives me the result of the newly created “Crosstab Query”. The “TotalArticles_Crosstab” gives me the results for each item, plus a column with the total:

There is no data for the first half of the year.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats