98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article
Access articles
How Do Cross Tabs And Pivot Tables Differ In Microsoft Access?
Thu 26th May 2011
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.
Author is a freelance copywriter. For more information on microsoft access course, please visit https://www.stl-training.co.uk
Original article appears here:
https://www.stl-training.co.uk/article-1707-cross-tabs-and-pivot-tables-differ-in-microsoft-access.html

London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsBritish Home
Clinical Lead Nurse Patience Andes Word Intermediate Very informative training i recommend it to all staff PML (Programme Mngt) Ltd
Mngt Consultant Mark Harrington Excel Introduction A perfect introduction to excel - whilst I am glad that I started out with doing the beginners course, as it has given me a refresher, I believe that I will need and want to sign up to the intermediate course which will more likely cover some of the advanced formulas that I have to work with or pick up for my job. But this has been a great way of getting me into the right 'head space' for the intermediate stage, and Sarah is a really good trainer at maintaining a pace that everyone can keep up with and remaining patient. She made everybody comfortable and made it simple to understand and follow the7stars
Media Planner / Buyer Tom Chard Excel Advanced Marius was an excellent teacher - enthusiastic, passionate, extremely understandable, went through things at an appropriate pace, knowledgeable, approachable and friendly. Am already looking forward to the next course! Maybe next time there could be a sense of competition? I don't know - in my previous training sessions an element of competition has always engaged people fully. |
PUBLICATION GUIDELINES