Arbitrary number of columns

[lution]

Not sure if this should be a query question or a report question so I'm starting here. I'd like to create a report for the number of tickets each officer wrote within a date range. There are multiple ticket types (examples: MV4016, Parking, but there could be more). I can generate a query that groups on officer and ticket type and give me the count and then put that into a report so that each officer has a single line per ticket type.

Example:

Officer1

MV4016 3
Parking 2
----------------------
Total 5
​

Officer2

MV4016 10
Other 4
Total 14
​

However, I'd like to conserve space and have the officer and each ticket type as columns:

Officer MV4016 Parking Other Total
Officer1 3 2 0 5
Officer2 10 0 4 14

I could hard-code the ticket types for now but looking into the future, I'd like it to be flexible so if they add a new one, I don't have to rewrite the query/report but I'm not sure that's possible?

 

[JHB]

Make a crosstab query.
If you don't can get it, then show sample data from you table/query.

 

[lution]

Thanks JHB, that did the trick. Can't believe I've been using access for 10+ years and this is the first time I've dealt with a cross tab.

 

[JHB]

Never for "old" to learn something new.

Crosstab is a very powerful tools and safe you a lots of time, (when you've to set it up by coding it yourself).