Pivot tables in Access are very powerful. The major flaw is presentation. I know exporting to excel is an option, however I want to merge many pivot tables into one report in Access. Is there any way to convert a pivot table (and multiple pivot tables) from a query, into one aggregated report view for presentation?
-
-
There is no way to do this directly in Access itself, I'm sorry to say, unless you are considering just joining the underlying crosstab queries together.
I do a lot of statistical reporting using crosstabs from Access exported to Excel workbooks. These queries use static heading layouts and row orders so that when they are copied to the Excel workbook they can be treated within the workbook as base tables for subsequent reporting, presenting the underlying data either in specially-formatted tables on other pages of the workbook or in chart form.
I run the export stages automatically in my case but the same principle applies if you are simply copying and pasting crosstabs into Excel manually; use the Access data as a starting point for presentation, not an end-point, as Access does not of itself provide all of the means to report crosstabbed data in ways that make most sense to users.
As I mentioned, it is possible to join multiple separate crosstab queries on a common key value to overcome the one-column pivot limitation which is inherent in Access, but this is only a partial solution to any presentation issues you may have, and can introduce its own performance problems when joining complex crosstabs.
Welcome to Bytes!
-Stewart
PS I have concentrated on crosstabs, which are the built-in means to pivot a column across rows. Access also provides pivot table views of data, superficially similar to the pivot tables that can be done in Excel. I have not mentioned them in my main reply because a pivot table view in Access is imposed on the underlying data in the same way as a chart would be - as an aid to presenting it. These cannot then be merged with others or otherwise used as the basis for further queries as far as I know.
Comment