help with frequency counts in several fields

[Guest]

Hi,
I have a table with 5 fields containing random numbers as values (sort of
like the lotto). is there a way to have a query to show the frequency of the
values of each fields?
e.g.
field1 field2 field3 ...
1 5000 70
2 2 70
1 5000 80
100 444 800

1 count 2
2 count 1
100 count 1
5000 count 2
2 count 1
444 count 1...

Thanks,

 

[Guest]

Jeff,

Try something like:

SELECT 1 AS Field, Field1 AS FieldValue, Count(Field1) AS Freq
FROM tblTest
GROUP BY 1, Field1
UNION ALL
SELECT 2 AS Field, Field2 AS FieldValue, Count(Field2) AS Freq
FROM tblTest
GROUP BY 2, Field2

You will need to add additional UNION and Select statements for the third
thru fifth fields.

Dale

 

[John Spencer (MVP)]

I would try the following.

SELECT Field1, Count(Field1)
FROM (
SELECT Field1
FROM TableName
UNION ALL
SELECT Field2
FROM TableName
UNION ALL
SELECT Field3
FROM Tablename
UNION ALL
SELECT Field4
FROM TableName
UNION ALL
SELECT Field5
FROM TableName) as QUnion
Group By Field1

IF that fails try making the Union query as a separate query, saving it, and
then using that in a totals query.