The first thing to note when creating an Access Pivot table or a crosstab Ms Access Query is that they both handle the grouping and summarising of ‘detailed’ records which means in most cases this type of output reports are driven by the typical ‘SELECT’ based query.

This article is not to show you how to create pivot tables or crosstab queries but how they differ; helping you to understand the key differences. If you want to know how, take a quick look at how to create Pivot tables with this simple video tutorial.

The main difference between the two is down to design capability and the end-user flexibility. They both produce the same type of report!

Access Pivot Table Versus Access Crosstab Query – Which One?

You create an Access Pivot table from either a table but normally a query based on related tables which has a quick and simple output view called ‘PivotTable View‘ (as well as one for a ‘PivotChart View‘) taking you straight into an interactive canvas area where you can drag and drop fields between one of the four sections available.

Alternatively, you can create PivotTable (and PivotChart) from an Access Form, which opens up more functionality and in particular being able to add either VBA or macro code to their events.

Both methods makes it very easy for end-users to add and remove fields and re-arrange orientations along side the ability to filter and sort data too and is the obvious choice during run-times.

Regarding Crosstab queries, this a special type of query normally created using the wizard tool again, via other queries but has some restrictions when comparing this to a pivot table report.

This query allows only one column heading and up to three row headings and don’t forget to add at least one value element with your chosen aggregate function i.e. Sum.

Since it’s just a query, there is very little formatting available and users would need to create an Access report to it in order to add the rich formatting. Also, every time you want to change the orientation or pick alternative fields (if present), then users must go to its design mode which is a scary place if you are just the operator (user) of the database and have little knowledge being ‘under the hood’!

As you can appreciate, most developers and end-users alike prefer the Access Pivot Table just because it’s more adaptable and since it is driven by other queries, you are not restricted with the properties of adding complex criteria and expressions too.

Which one do you use? Why not  use the reply box below and tell me what restrictions you have discovered when using Crosstab queries or if you find Pivot tables more challenging.

Tags: access crosstab query, access pivot table, how to create a query, MS Access Query, using crosstab wizard, working with pivot tables