Microsoft Access can show grouped and summarised data in two similar ways, using Cross Tabs and Pivot Tables. Cross Tabs are created use queries and Pivot Tables can be created directly from tables or queries, or by creating a Pivot Table on a form. This article describes how each are created and summaries their differences. Cross Tabs and Pivot Tables both sort and group data and produce a neat row by column table with a calculation for each row and column intersect.

Let's first look at creating a cross tab by using the cross tab query wizard. Once launched the wizard lets you select a table or query the cross tab is based on. You then choose at least one field for the Row Heading, another for the Column Heading and a third for the data analysis. When complete, the cross tab query groups the rows and columns data in a table layout showing one row for each data value from the row heading, one column for each data value for the column heading and the calculations for each row and column intersect.

If you want to change the cross tab design you need to edit the query in design view, and select, for example, different fields for the row and column headings, or a different calculation for the intersections, but it can be more difficult to edit a cross tab query compared to using the cross table wizard builder.

A Pivot Table shows exactly the same data analysis as for the cross tab. A Pivot Table can be built by using the form Pivot table wizard, which asks you in exactly the same way for at least one row field, one column field and one data field. Once complete the resulting table shows the same data analysis as achieved by the cross tab. However the key difference is that the resulting fields on the pivot table can be dragged and dropped on the form to create new instant arrangements - hence the name pivot table, with no need to switch to design view.

So the Pivot Table tends to be much more flexible and easier to use and edit, and avoids the need to make design view changes. You can even pivot table a pivot table if you wish by changing the view of an existing form based pivot table.

You can also create a Pivot Chart from a Pivot Table to show the pivot table row and column groupings visually in, for example, groups of vertical columns, and the sizes of the columns set by the pivot table calculation data. If you then drag and drop the Pivot Table groups to change the analysis, the Pivot Chart also changes to show the new arrangements.

You can also create a Pivot Table without going near an Access form, because tables and queries have their own built in Pivot Table view. So once you've created a table or query in Access, click on the pop down showing the choices of view. Look carefully and you'll see that both tables and queries contain a choice of Pivot Table View. Choose this and you're taken the same Pivot building view you see using the form wizard, with the fields from that table or query listed, so you can drag and drop these to the same row, column and data parts of the pivot table and again see instant results. So this view lets you build the pivot table in exactly the same way, but the results are saved within the table or query itself, rather than in a separate form, and can always be viewed by switching to the Pivot Table view from within the table or query. The only difference between the table view Pivot Table and the Pivot Table form is that the form is visible immediately whereas the table view has to be selected.

Once you've created an Access Pivot Table either via the form builder or within the table or query view, the pivot table can be easily be exported to Excel for further analysis. If you do this, the pivot table is copied to Excel and becomes an Excel Pivot Table. You can use Excel many analysis features, for example to sort, filter or further group the data.

Hopefully this short article has given you a brief insight into the differences between an Access Cross Tab query and a Pivot Table. If you're interested in learning more about Access and its many data analysis features why not consider attending a training course, and really take your Access skills to the next level.