I have six columns that have numbers in them. I would now like to combine this columns into one so I can have a report that groups the numbers together and gives me a count.

Example of my columns

col 1 col2 col3.....etc
5 6 5
3 5 6

So I could get an output of.........

number qnty
5 3
3 1
6 2

Or any suggestions on how I can group the numbers in seperate columns to get a count on them.

Thanks,

Kacy

If you database were structured differently it would be far easier. If this is a one-time thing, I'd output the first column of numbers to a temp table, then I'd append columns two and three to that same temp table. Then I'd run a totals query grouped on the numbers.

If you need to do this more than once, I'd consider changing the data structure. Barring that, I'd create a union query to pull the three columns into one and run a totals query off that.

I tried that early and it didn't work, but I just ran across something that said Unions by default include distinct and to use the "union all" predicate.

Got it working.

Thanks,
Kacy