Grouping data on my Report from option group field

[jsanbor1]

Hello, I've been lurking & searching this forum for weeks and you guys have been a great asset.

OK - I have an option group that saves 5 options as 1 through 5. I currently have my report set to change the numbers to the text that I want with an unbound text box using IIf and that works pretty well. 1 = App, 2 = NI, 3 = CB, -> =(IIf([OptResult]="1","App"," ",.....))

However, I need to have a seperate column for each option on my report and sum the totals.

ex.

App NI CB
12 2 20

Any tips on how to do this?

thanks for any replies,
jj

 

[dhookom]

I think you are looking for a crosstab query. Your column heading expression would be something like your IIf(). I would recommend using Choose() rather than nested IIf()s.
Choose(OptResult,"App","NI","CB",...)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.