Unique patients 2

[pgh2377]

I have a table that has prescription information. The patient may be repeated more than once. Using I run two queries that count the dollar amount and the other filters out unique patients so I get unqie patients and total billed. Must I always have two queries or is there any other way, i.e. one query? that counts unique patients and sums billed expenditures.

 

[scriverb]

I need a little more information before I can write this for you. What are the field names, indexes, how to identify same patients(i.e. Name?? or ID#??) etc. Post back with this information and we can come up with a single query to do this.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[pgh2377]

patientrn is the patient identifier
rx is the prescription number
drug is the drug name.
billed is the cost

I'm trying to creat a query that counts the number of unique patients and sums the billed amount per drug in the same query. Can it be done?

 

[scriverb]

Let's see if this will give you what you want:

Select A.[Drug], Count(A.[patientrn]) as Patient_Count, Sum(A.[billed]) as Sum_Of_Cost 
FROM [red][i]yourtablename[/i][/red] as A 
GROUP BY A.[drug]
ORDER BY A.[drug];

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[pgh2377]

But will the count give me unique patients?

 

[scriverb]

No not at this time. I am working on it and we shall see. Kind of tricky.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[DanJR]

I'm not sure if I fully understand the data in your underlying table, but suppose you have a table like this:
[tt]
patientrn rx drug billed
1 1 A $10.00
1 1 A $10.00
1 2 A $10.00
1 3 B $5.00
2 4 F $23.00
2 5 B $5.00
3 6 A $10.00
4 7 F $23.00
5 9 A $10.00
[/tt]

Then the following query:

SELECT B.Drug, B.PatientCount, A.SumBilled 
FROM
  (SELECT Drug, Sum(billed) AS SumBilled 
   FROM tblDrug 
   GROUP BY Drug) AS A,
  (SELECT A.Drug, Count(*) as PatientCount
   FROM (SELECT DISTINCT Drug, Patientrn  
         FROM tblDrug) AS A
   GROUP BY A.Drug) AS B
WHERE A.Drug = B.Drug;

will produce this:
[tt]
Drug PatientCount SumBilled
A 3 $50.00
B 2 $10.00
F 2 $46.00
[/tt]

Drug A occurs 5 times but only for 3 different patients. I would be much easier if MS Access implimented the ANSI Count(DISTINCT ...)

does this help?

Cheers,
Dan

 

[scriverb]

Dan, Star for you. Thanks for helping out here. I feel silly as I was mistakenly working in A97 to do this and the the query design features of SQL there do not work as they do in A2k. I had multiple versions opened and couldn't get similar code to work when I knew it should. As soon as I changed over to A2k all was well. I am sure PGH2377 will be pleased.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[DanJR]

Thanks Bob

 

[lespaul]

Definitely worth a star Dan!

I suggested using Count(DISTINCT ...) to someone the other day (just taking a wild guess) and when told it didn't work thought that I guessed wrong, but now I know that I can use it other places, just not Access!

Thanks!

Leslie

 

[pgh2377]

i'm an idiot, i can't get it to work, here are my fields:

Category: to be group by
mbr_id: want to count unique patients


?? help

 

[scriverb]

You originally posted:

patientrn is the patient identifier
rx is the prescription number
drug is the drug name.
billed is the cost[/code]

Now you are changing tables and fielsd. Please give us all of the fields to use and rollup.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]