Count number of unique users

[Guest]

I have a table PHP3014Rnew with the fields [Drug Desc], [Cardholder ID],
[NDC],[Formulary Flag], and [Claim Type]. Each [Drug Desc] may have more than
one [Cardholder ID] associated with it, since a patient may take a drug more
than once. I need to find the number of UNIQUE users of each drug. Here is my
SQL. I can't modify it to display the number of unique [Cardholder ID] for
each [Drug Desc]. Can someone help, please?

SELECT DISTINCT PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID],
PHP3014Rnew.[NDC], PHP3014Rnew.[Formulary Flag], PHP3014Rnew.[Claim Type]
FROM PHP3014Rnew
GROUP BY PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID],
PHP3014Rnew.NDC, PHP3014Rnew.[Formulary Flag], PHP3014Rnew.[Claim Type]
HAVING (((PHP3014Rnew.[Claim Type])="P"));

 

[Douglas J. Steele]

Are [NDC], [Formulary Flag], and [Claim Type] dependent on [Cardholder ID]?
If so, they shouldn't be in the PHP3014Rnew table.

The only sure way to get distinct [Cardholder ID] for each [Drug Desc] is to
only query on those two fields:

SELECT DISTINCT PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID]
FROM PHP3014Rnew
WHERE PHP3014Rnew.[Claim Type])="P"

 

[Guest]

Thanks, Douglas. Cardholder ID represents the user, while NDC and formulary
flag are associated with the drug desc. The data comes from an external
source.

The code below only lists the drug and each user. I need to know the total
of unique users of each drug. Suggestions?

Are [NDC], [Formulary Flag], and [Claim Type] dependent on [Cardholder ID]?
If so, they shouldn't be in the PHP3014Rnew table.

The only sure way to get distinct [Cardholder ID] for each [Drug Desc] is to
only query on those two fields:

SELECT DISTINCT PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID]
FROM PHP3014Rnew
WHERE PHP3014Rnew.[Claim Type])="P"

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a table PHP3014Rnew with the fields [Drug Desc], [Cardholder ID],
[NDC],[Formulary Flag], and [Claim Type]. Each [Drug Desc] may have more
than
one [Cardholder ID] associated with it, since a patient may take a drug
more
than once. I need to find the number of UNIQUE users of each drug. Here is
my
SQL. I can't modify it to display the number of unique [Cardholder ID] for
each [Drug Desc]. Can someone help, please?

SELECT DISTINCT PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID],
PHP3014Rnew.[NDC], PHP3014Rnew.[Formulary Flag], PHP3014Rnew.[Claim Type]
FROM PHP3014Rnew
GROUP BY PHP3014Rnew.[Drug Desc], PHP3014Rnew.[Cardholder ID],
PHP3014Rnew.NDC, PHP3014Rnew.[Formulary Flag], PHP3014Rnew.[Claim Type]
HAVING (((PHP3014Rnew.[Claim Type])="P"));