Crosstab Query is a type of select query that calculates sum, average and other aggregates functions and then group the results into two sets of values. One, which is down the sides of the datasheet and another, is across the datasheet. You can also think of it as two dimension of a table.
There is an option of cross tab query wizard but it is limited with what you can do. So, it’s better to create your crosstab query from design view.
Well you can create cross tab query in design view also and it’s quiet better option too. As design view allows you more control over query design. It supports features that are not found in query wizard. So learn how to create a crosstab query in design view.
Click Query Design option from the Create tab in the ribbon.
Select the table that you want to insert in the query and click Add To option add it to the query.
After adding all the table in the query, click close to close the dialog box. In the shown figure we have added the order table and the order details table.
Tap to the Crosstab option from the Design tab.
This will switch the query to a crosstab query. You can take view of the options in the bottom query pane change when it is in crosstab mode.
In normal select query, you can double-click a field in the top pane to make it appear in the bottom pane.
Here, in this example, we are going to use some of access in-built functions to aggregate some fields and also to display another fields and to display fields in easier way.
We have used the Format() function to display month section of the order date. Also the sum() function to perform a calculation on the Unit Price, Quantity, and Discount fields.
Tap to the run option from the ribbon to run the query. Alternatively, just switch to datasheet view. You can watch the query displays the value of the sip city field as row header and the result of Format () function as column header.
Sum() function result will appears wherever there is a value for the month.
