Counting Unique records, multiple fields 1

[platypus71]

I think my answer is to use multiple queries or a report, but figured I'd ask the experts before giving up on the single-query idea.

I have a table that has 11 fields in it.
Field1 is used for grouping.
Fields 2-11 are called "Level 1", "Level 2", "Level 3", etc.

What I am trying to do is count how many unique entries are in each level grouped by Field1. So far the only way I've found to do this is with 10 seperate queries, each query looking at Field1 and one of the Level fields and just grouping them. Then, creating another query that counts the results of Field1, grouped by Field1.

Is there a way to do this in a single query, or is it just not possible?

 

[lespaul]

you need to create a union query that normalizes your data and then you can use that as the source of a single query to count:

SELECT Field1 As GroupName, "Level2" As Level, Field2 As Data FROM TableName
UNION
SELECT Field1, "Level3", Field3 FROM TableName
UNION
...
SELECT Field1, "Level10", Field10 FROM TableName

then you can just do:

SELECT GroupName, Level, Count(*) FROM qryNormalize GROUP BY GroupName, Level

HTH
Leslie

 

[platypus71]

Funny thing was, when I read your response, I was like...DUH.

Thanks for the help.