Query problem - How do I do this?

[No-One]

I have a db that has only a single table.
Each record contains 55 fields.
Most of the fields contain a value of 1, 2, 3, 4, or 5.
The rest are text fields that will simply be printed.

What I want to do is run a query that will provide me with a count each 1 in
field1 and each 2 in field1 and each 3 in field1 and so on for each field.
So I want it to display like this:
field1 countoffld1
1 6
2 11
3 7
4 6
5 20

Now, I've been able to do the query but only on each individual field.
Whenever I try to combine the queries I end up with a long listing that
display mulitiple rows with field1 that contain a value of 1 and then its
count beside that with field2 and its count beside that.
So what I end up with something like this:
field1 countoffld1 field2 countoffld2
1 1 1 1
1 2 1 2
2 1 1 3
1 3 2 1

and so on...

How the heck can I get a count for each value, in each field, in the same
query?

 

[Duane Hookom]

If I understand correctly, a properly normalized table would help. You might
want to look at "At Your Survey" at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. There is
a report in AYS that shows counts of each response value by question.

You may be able to normalize your table with a union query
SELECT 1 as Fld, Field1 as TheValue, Count(*) as NumOf
FROM tblTooManyFields
GROUP BY 1, Field1
UNION ALL
SELECT 2, Field2, Count(*)
FROM tblTooManyFields
GROUP BY 2, Field2
UNION ALL
SELECT 3, Field3, Count(*)
FROM tblTooManyFields
GROUP BY 3, Field3
....etc...