Question Crosstab with more than one header row (1 Viewer)

Jalnac74

Registered User.
Local time
Today, 06:00
Joined
Apr 4, 2015
Messages
23
Hi all,

I need to create a query/report that basically allows the user to see data where there are 2 header rows. something like

criteria 1
a b c
item 1
item 2

Does anyone know if this is possible?

Apologies if the 'table' above does not look right, I'm not sure if it can be created on a forum post.

Thanks
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 13:00
Joined
Feb 19, 2013
Messages
16,553
not quite sure what you are asking, but a crosstab can only have one 'header' column such as budget, actual, forecast, etc

If you require more, then you need to create multiple crosstabs - one each for budget, actual, forecast which you then join together in a final query on (in this example) a unique id such as an account code
 

Jalnac74

Registered User.
Local time
Today, 06:00
Joined
Apr 4, 2015
Messages
23
Thanks CJ and Plog. I thought that would be the answer but I wanted to check on the forum first whether it could be done in Access. I will have a look into exporting to an Excel Pivot table to get what I need.

thanks to you both!
 

stopher

AWF VIP
Local time
Today, 13:00
Joined
Feb 1, 2006
Messages
2,396
As CJ mentioned, you can only have one column header. But you can quite easily create a new pseudo header by concatenating the two fields you want.

So in your example if you have a field called Criteria (e.g. Crit 1, Crit 2 etc), and another called Letter (a, b c). If you then create a new field like this:

MyHeader: Criteria & "-" & "Letter"

So you will get values like this:

Crit 1 - a
Crit 1 - b
Crit 2 - a
Crit 2 - c
etc

An you can now create a cross-tab using this field.

hth
 

stopher

AWF VIP
Local time
Today, 13:00
Joined
Feb 1, 2006
Messages
2,396
The best way to achieve this is to export your data to Excel and then create a pivot table (https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576). Cross-tab queries allow you to convert the values of one field to column names, but not more. Pivot tables easily achieve what you want.

When you look at the link provided by plog, you will see there's an option to link using a connection. I highly recommend you do this. i.e. open a new spreadsheet and create a "connection" to your chosen table/query/database. This gives you seamless connections to the Access data (just by simple refresh) rather than messing around with importing.

If you only need one pivot to do a specific report I would try to keep it all in Access. But if you are doing lots of ad hoc pivot and report analysis then connecting Excel to Access and doing to crunching in Excel is a great way to go.
 

Jalnac74

Registered User.
Local time
Today, 06:00
Joined
Apr 4, 2015
Messages
23
Hi,

Stopher, I will try both of your suggestions. Once I have a workable solution I'll repost on here just in case others have the same issue.

J
 

Users who are viewing this thread

Top Bottom