Query to count errors when records duplicate

[mkallover]

I'm trying to write a query to count unique errors on prescription claims submitted by pharmacies but I'm having an issue: A pharmacy can submit a claim multiple times and it can have multiple errors associated with it. What I would like to do is count only each unique occurrence of the error based on the Prescription ID.

CUST_ID PRESCRIPTION_ID ERR_CDE
300 921928 256     
300 921928 256     
300 123456 123
300 123456 256     
300 123456 123
300 123456 123
320 954207 136     
320 954207 136

But in reality what I want to count is 1 occurrence of errors 123, 136 and 2 occurrences of 256. I already have a query that totals up all errors by CUST_ID but I'd like to modify it so that it will count only unique errors based on distinct PRESCRIPTION_ID.

Here's the query I'm using:

TRANSFORM COUNT(ID) AS [COUNT]
SELECT CUST_ID
FROM tblClaims
WHERE [ERR_CDE] IS NOT NULL
GROUP BY CUST_ID
PIVOT [ERR_CDE];

ID is just the unique ID for each record.

Any thoughts on this one?

[June7]

Second time today I have offered this query structure as a suggestion:

SELECT Err_Cde, Count(Err_Cde) AS CountOfErr_Cde
FROM (SELECT Prescription_ID, Err_Cde
FROM Table1
GROUP BY Prescription_ID, Err_Cde) As Query1
GROUP BY Err_Cde;