Cross Tab Queries in MS Access

Creating Cross Tab Queries In Access 2007

The following process is used to create Cross Tab Querie:

  • Open You database file in Access 2007.
  • Click on Create Manu Tab form Office Ribbon then Select the Queries Wizard icon Form Other group.
  • Select The Query Type As Cross Tab Query Wizard.
  • The Cross tab query dialog box appear on screen.
  • First screen Select The Table name from which you want to create query.
  • In Next Screen Select the Row heading.
  • Next Select The column heading.
  • Now Select which column you want to calculate the values and select the function for example Sum.
  • Give the Name and Click On finish Button.

Some step in this tutorials are simple but some step required some explanation. For example what is Row Heading and column heading.

If we have a table with some data:

cross tab query

Row Heading:

In This section we select the Table Field which display as row heading. you can select one or more row heading. From above table we select ID and Customer_Name as Row Heading.

cross tab query

Column Heading:

This cross tab option is represented horizontally in your dataset. Good candidates for this grouping are sales quarters or other categories of data for which you want only one grouping aggregation, because only one column heading is allowed in a cross tab query. From above table we select Payment_Month as Column Heading So the value of This field Display As column Heading .

In the end Payment filed from above table we select as calculated filed and sum as our function. The value of This filed are display in Column and row intersection.

The final result of our cross tab query is :

cross tab query

You can see the Name are display in the row heading. Each value of the Field Month Converted in to Column and The Payment values are display in the Column and row intersection.