Count more then one column in a query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to count more then on column in a query and still have
them grouped by each column.

So lets say I have 14 columns called Q1-Q14. I need to count how many times
each number in the column appears for each column. Each column can have a
number between 0 and 6.
 
I need to be able to count more then on column in a query and still have
them grouped by each column.
Click to expand...

A Count counts *records* not values.
So lets say I have 14 columns called Q1-Q14. I need to count how many times
each number in the column appears for each column. Each column can have a
number between 0 and 6.
Click to expand...

If you have fields Q1 to Q14, your table structure is not properly
normalized; and the need to group by all of them reinforces this
judgement. You are embedding a one to many relationship within each
record, and this makes it very hard to design good queries of the type
you request, since the SQL query language was designed to work with
normalized data!

I'd suggest a normalising Union query followed by a totals query:

SELECT (1) AS QNum, Q1 AS Q FROM yourtable
UNION ALL
SELECT (2), Q2 FROM yourtable
UNION ALL
SELECT (3), Q3 FROM yourtable
UNION ALL
SELECT (4), Q4 FROM yourtable
....
UNION ALL
SELECT (14), Q14 FROM yourtable;

Save this as uniQ.

This will "string out" all the values into a tall-thin recordset.

You can then spread them out again with a count using a Totals query:

SELECT QNum, Q, Count(*)
FROM uniQ
GROUP BY QNum, Q;

John W. Vinson[MVP]
 
Back
Top