How to pivot on multiple fields?

Question

I have a table like this:

+----+--------+------------+----------+
| ID |  Item  |    Date    | Category |
+----+--------+------------+----------+
|  1 | xyz    | 3/12/2013  | A        |
|  2 | xyz    | 3/23/2013  | A        |
|  3 | j423   | 4/19/2013  | C        |
|  4 | 9d98df | 4/16/2013  | B        |
|  5 | j423   | 5/13/2013  | C        |
+----+--------+------------+----------+

I want to group the data in columns both by Date (by month) and by Category, like so:

       3/2013    | 4/2013
Item   A | B | C | A | B | C
xyz      |   |   |   |   |    
j423     |   |   |   |   |
9d98df   |   |   |   |   |

I know that I can group the data by having a function that returns the date as month/year and then pivot on the month field, and I can create a composite-key field with, e.g.

select item, getMonth(date) & category as month_category from myTable

and then pivot on month_category to give me:

Item   A_3/2013 | B_3/2013 | C_3/2013 | A_4/2013 | B_4/2013 | C_4/2013
xyz             |          |          |          |          |    
j423            |          |          |          |          |    
9d98df          |          |          |          |          |

But this won't give me a report where I have the two layers of columns shown above. Is there a way to do this in Access? Or am I stuck with the composite column names?

@Lisa: I'll take whatever approach will allow me to present the data in the two-layered column format. With a query I can only have one row of column headers, so it would have to be a report, right? — sigil, Dec 18 '13 at 16:19
Yes, it would have to be a report. MS Access does have a report wizard to walk you through the process, though. — Lisa, Dec 18 '13 at 17:58
What values are you looking to get out of said Pivot? A simple count of Items sold by Category and month? — craig.white, Dec 23 '13 at 14:13
Which field would you like to aggregate? I haven;t seen it ;( — Maciej Los, Dec 29 '13 at 00:34

score 1 · Answer 1 · answered Dec 29 '13 at 16:25

am I stuck with the composite column names

You are "stuck" with the composite column names in the crosstab query, but you can format the report using a group header with multiple stacked labels if you feel so inclined. Don't confuse the data (the results returned by the query) with the presentation of the data (the report layout).

score 0 · Answer 2 · answered Dec 29 '13 at 15:53

No, it isn't possible to get pivot query with two-line headers. To display data, use MS Access report. I would suggest to create pivot query data in that way:

Item  | Month  |A | B | C |
xyz   | 3/2013 |  |   |   |
xyz   | 4/2013 |  |   |   |

Then you'll be able to group or summarize data on month/item.

For further information, please see:
Create a grouped or summary report
Grouping records on date/time values in an Access report

How to pivot on multiple fields?

2 Answers2