Question multiple Column Heading in crosstab Access2010

[lokanathas]

Hi,

i want to added multiple Column Heading in crosstab, but its through an error only on Column Heading can be added.

Basedata
Row Heading1 Row Heading2 Column Heading1 Column Heading2 Value
01-Apr-17 5101 Outright 37-SPRING-2017 1
01-Apr-17 5101 Outright 37-Summer-2017 5
01-Apr-17 5101 SOA 37-SPRING-2017 2
01-Apr-17 5101 SOA 37-Summer-2017 1
01-Apr-17 5101 SOA 37-SPRING-2017 2

Output required in crosstab

Outright Outright SOA SOA
Row Heading1 Row Heading2 37-SPRING-2017 37-Summer-2017 37-SPRING-2017 37-Summer-2017
01-Apr-17 5101 1 5 4 1

 

[plog]

You only get one column heading in a cross-tab. I suggest you create a query, export the data to excel and do a Pivot Table:

https://support.office.com/en-us/ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576

 

[arnelgp]

Actually you can but you can't sequence it the way you wanted.

 

[isladogs]

Sorry to disagree with my esteemed colleagues.

If you are talking about a crosstab report, you can do this and sequence it the way you want by using a dynamic crosstab report and a bit of trickery.
Whether its worth the effort is another matter

See here for an example of a dynamic crosstab report:
https://www.access-programmers.co.uk/forums/showpost.php?p=1537880&postcount=1

The trickery would include concatenating the fields then parsing the text into sections so part of it is used in row 1, another part in row 2 and the rest in row 3.

NOTE: this could be a bit tricky if you've never done dynamic crosstab reports before.

 

[arnelgp]

Sorry but I did not see in the link

 

[isladogs]

There's a rather large image of a report in that post....
The zip file includes the code for that report.

I have many other examples including some posted elsewhere on this forum.

 

[DevTycoon]

[solved] pivot table style

I would think this is super easy in pivot table connected to access via adodb connection or something.


I added 5000 records to your picture and returned a pivot table that displays your output.

No code


Edit::
I uploaded a version 2 spreadsheet that kept the 5000 records but limited the row and column categories to things that have random id between 1 and 3 for a smaller time frame. The pivot table looks dope now my friend.

If you want to connect the pivot table to sql server or access database then you have yourself a nice real time reporting tool.

 

[isladogs]

Interesting .... I wish I knew how to do that ....

I've never really got to grips with Pivot Tables / Charts & the incentive to do so disappeared when these were removed from Access

Maybe a tutorial would be helpful!

 

[DevTycoon]

Did the OP get the problems solved?