“Crosstab Query”

A “Crosstab query” restructures the result of the data by grouping them together horizontally and vertically, so that they are easier to read. You can compare it to an “Excel” sheet.
A “Crosstab Query” calculates the sum, average or other functions, and groups them into two sets of values.
One horizontally and other vertically on the data sheet.

A “Crosstab Query” can be done in two ways, first by clicking on the “Query Design” button in the “Ribbon” and then by changing it into a “Crosstab Query”.
A second way is to use the “Query Wizard”.
In this example, I use the “Query Wizard”.
So I select the tab “Create” in the “Ribbon” and click the “Query Wizard”.
In the “New Query” I select the second option “Crosstab Query Wizard” and click the OK button.
This opens the “Crosstab Query Wizard” dialog.
In the dialog box, choose the table, query, or both, from which the information must come.
In this example I select the “TotalArticle” 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 “Article” 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 should 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 the from the functions list on the right side, I select “Sum”.
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 “TotalArticle_Crosstab” gives me the results for each item, plus a column with the total amount:

Featured Courses

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