Crosstab Query Example 1 [download  database]

This is how the raw data looks like. Notice that there are duplicate part numbers. It is because the parts were sold in different periods, as you can see the dates and qty sold were not the repeating for the similar part numbers. This is difficult to read and we need some form of a cross-tabulation. Crosstab query will do just this.

You will do the following steps to complete the crosstab query without using the crosstab wizard dialog box. You can have up to 3 Row Headings, and only allowed one Column Heading.

1. Add the table 'Past6MonthsSales' to the query grid.
2. Use the Query Type drop-down list to select Crosstab Query.
3. Add the field PartNumber as the Row Heading, add 'DateSold' as the Column Heading and use interval datatype for the , the 3rd field using the SUM aggregate function to compute the Values, and finally use the LIKE operator, for this example, to include only those part numbers where they don't end with a suffix B. Then run your query.

This is the result after running the above crosstab query. Now there are no repeating part numbers, the 6 months were pivoted to the columns, and also the sum values of parts sold over the time periods. If you don't use fixed column headings, all the columns are included in the query output in alphabetical order as you can see in the diagram below. By using fixed column headings, you can tell Access to re-arrange the order of the columns in the query result.

To go to fixed column headings, place your cursor on the date field 'DateSold', then click on the Properties icon in the toolbar, or simply just right-click on the date field. Next it will bring out the field query Properties box.

In the Column Headings field, type in the order of the month-year periods that you want them to appear in the query result. In this example, I want it to appear in such sequence from 2006 Jan to Mar, then from 2005 Oct to Dec. Close the Properties box after you are done and run the query again.

Now you see Access re-arranged the time periods to appear just as the order that I wanted. Take note if you type in the different header names, there will be no data appear in the query result. If you type in only some correct column header names, you will be shown only query result for those few columns that you had specified.

In SQL VIEW, this is what it looks like.