Access Pivot Table: losing Detail Data when Exporting to Excel

[MichaelSchulz]

I used Access to create a Pivot Table from the returned record set of a query.

The Pivot Table displays exactly what I want: it shows in the Detail Data field each and every data point at the intersection of the Row area and the Column area without any sort of summarizing. I know that the point of a Pivot Table is to aggregate the data in some way but in this case it just so happens that I need to see the detail just as Access shows it.

The problem is when attempting to export the table to Excel: the Detail Data field empties and, it seems, Excel cannot produce what Access so easily created. This seems to be because Excel is required to perform some sort of calculation to aggregate the data in some fashion.

My thinking is: if Access can so readily create exactly what I need, there ought to be a way to export that table.

How can this be accomplished?

Failing that, how can I produce an output, preferably to Excel, that organizes the data in the same manner?

[June7]

What is the structure of raw data? Maybe CROSSTAB query could produce the desired output.

[MichaelSchulz]

I don't believe a CROSSTAB would work as it requires a function to aggregate and summarize the data.

The source data for the Pivot is the result of a query that contains joins and nested (or 'correlated') queries. The structure of the query result in Datasheet View is:

(PIP ID, Project Name, Activity, Control, Date of Change, Old Value, New Value)

where PIP ID is the Primary Key for one of the source tables, and Control acts as a qualifier (or subset) of Activity.

[June7]

First(), Last(), Max(), Min() are also aggregate functions that can be used in CROSSTAB or GROUP BY query. What you want can possibly be generated with expressions that emulate a CROSSTAB (http://www.datapigtechnologies.com/f.../crosstab.html) then apply GROUP BY with one of the above functions.

How many Control values are there? I see only 2 (PS and PF).

[MichaelSchulz]

There are only two values for Control

[June7]

Try the CROSSTAB emulation?

[MichaelSchulz]

Hmmn. Well, that might work as a make-do, though the result is nowhere as clean or as readable as the Pivot.

I do thank you for the suggestion; I appreciate it. I may be able to at least move forward to some degree with this.

Still…rather hoping for something better.

[June7]

Note that pivot tables/charts removed from Access 2013. Can't build them but should be able to read if built in older version of Access. MS thinks should export raw data and do pivots in Excel.