CODEWITHSUNDEEP
sqlms-accesscount
JT2013
JT2013

Reputation: 643

Counting unique values in rows and columns in MS Access query

I currently have a table that looks like the following: 

[BUSINESS_PROCESS_ID]   [Error Type 1]    [Error Type 2]     [Error Type 3]

     PVT02                  108                                  93
     PVT02                   98   
     PVT02                   80              80                  80
     PVT02                   80              
     PVT02                   93   
     PVT02                   27              80
     PVT03                   21   
     PVT03                   102  
     PVT03                   80   
     PVT03                   80                                  102
     PVT03                   80   
     PVT03                   80   
     PVT03                   71

Based on each Business_Process_Id, i'm looking to write a query that counts each unique error type. Ie. the query output should look like the following: 

[BUSINESS_PROCESS_ID]    [Error Type]  [Count of Unique Error Types Combined]

     PVT02                  108                     1
     PVT02                   98                     1
     PVT02                   80                     5
     PVT02                   93                     2
     PVT02                   27                     1
     PVT02                   98                     1
     PVT03                   21                     1
     PVT03                   102                    2
     PVT03                   80                     4
     PVT03                   71                     1

As you can see, I'm not concerned with whether or not the count is by Error Type 1, 2 or 3. I'm looking to get the count of UNIQUE Error Types (ie. 98,80,108 etc.) by Business_Process_ID.

Could someone help? Thank You.

Upvotes: 1

Views: 2845

Answers (1)

Tim Lentine
Tim Lentine

Reputation: 7862

One way to accomplish this would be to use a UNION query to first put all error types into a single column. Then, you can use a simple query to aggregate the results into how you wish to see them.

SELECT BUSINESS_PROCESS_ID, [Error Type 1] as ErrorType
FROM TableName
UNION ALL
SELECT BUSINESS_PROCESS_ID, [Error Type 2] as ErrorType
FROM TableName
UNION ALL
SELECT BUSINESS_PROCESS_ID, [Error Type 3] as ErrorType
FROM TableName

Save this as a query.

Then, use this query in a aggregation query similar to the following:

SELECT BUSINESS_PROCESS_ID, ErrorType, Count(ErrorType) as Number_Of_Errors
FROM MyUnionQuery
GROUP BY BUSINESS_PROCESS_ID, ErrorType

Upvotes: 3

Related Questions