Cross tab query with sub column headings? Is it possible?

[Jameo]

Hi all,

I was just wondering if it was possible to create a cross tab query that has sub column headings.

For example, I would like a parent column, with a site name, and under that, have other options i.e

 _________________________________________________________________________________
|               Site Name 1              |              Site Name 2               |
|________________________________________|________________________________________|
|Max Deliverability|Actual Deliverability|Max Deliverability|Actual Deliverability|
|__________________|_____________________|__________________|_____________________|

There would be date and time row headings to the left of the above example. I've had a search around online but cant really find anything. Any help would be great.

Thanks in advance

P.S. Ultimately this then neesd to be exported into excel.

 

[Phildaburn]

If it's going to be exported to excel anyway, why not export the raw data the crosstab is going to use and take advantage of excel's more robust pivot table capabilities?

Phil...

 

[Jameo]

I can't do that unfortunately. My data is arranged such that all sites are in the same column, resulting in over two hundred thousand seperate rows of data. With the copy of Excel I have at work, the maximum sheet size is only 64000 rows. Simply put, there is far too much data to be exported to excel in that manner

 

[Phildaburn]

I don't know about Access 2010 - I don't have it - but crosstab queries will only permit you to use 1 field in a column heading. You can have multiple fields as row headings, but only 1 for the columns. Any possibility that your work will upgrade? or could they make an exception in your case?

Phil...

 

[SydneyGeek]

You don't have to export the data to Excel.
Build the pivot table in Excel, using Access as the external data source. Then the row limit is irrelevant.

Denis