Distinctive counting, can I do it?

[Schon731]

I have the following query that seems to work great for giving me the totals I need broken down by group.

SELECT [tbl 572 Group Home Analysis].[PT-Patient Group], Sum([tbl 572 Group Home Analysis].[TX-Acq Cost]) AS [Total Acq], Sum([tbl 572 Group Home Analysis].[TX-Price]) AS [Total Price], Count([tbl 572 Group Home Analysis].[TX-Rx Number]) AS [# of RX's], Sum([tbl 572 Group Home Analysis].[$ Margin]) AS [$ Margin], [Total Price]/[# of RX's] AS [Avg $ per RX], [$ Margin]/[# of RX's] AS [Avg Marging per RX]


FROM [tbl 572 Group Home Analysis]
GROUP BY [tbl 572 Group Home Analysis].[PT-Patient Group], [Total Price]/[# of RX's], [$ Margin]/[# of RX's];


I want to add a field [Patient Code] and count how many patients are in each [PT-Patient Group]. I cannot get this to work. For instance I have 1 patient in group CC that has 4 RX's. The RX Number count works and is 4 but in the Patient field I want it to be 1 to indicate there is only 1 patient in that group. Also, I dont know what the groups will be and they may change dynamically from week to week if that matters or helps. Thanks for any help..

[ajetrumpet]

I want to add a field [Patient Code] and count how many patients are in each [PT-Patient Group].

That can work simply by adding this to the sql:

select count([pt-patient group])

I have 1 patient in group CC that has 4 RX's. The RX Number count works and is 4 but in the Patient field I want it to be 1 to indicate there is only 1 patient in that group.

that doesn't make much sense. Could you re-tell the story please?


Also, if your groupings need to change often, run the query from a form with inputs to be indicated by a user. more than likely, if this is the case, creating and/or replacing the same query via VBA code will be much easier for you than parameter prompts.

[Schon731]

In the example I gave there happened to be only 1 patient with 4 items (RX's) I want it to count the number of patients in each group. In other words if there were 2 patient codes with a total of 7 RX's in group CC I want the Patient Code column or a column to show a value of 2 indicating there are 2 customers under that Patient Group.. Does that help??

[txrules]

Schon731,

I am not big on SQL I use the visual tools in Access and I might not be the biggest help but I have done similar things in access. I would make another query and just use the [Patient Group] and a Count of the [Patient Code]. Then I would have another query to put the two together. I did this in the query design mode then changed it to SQL. I hope it helps.


SELECT [tbl 572 Group Home Analysis].[PT-Patient Group], Count([tbl 572 Group Home Analysis].[PT-Patient Code]) AS NumberOfPatients
FROM [tbl 572 Group Home Analysis]
GROUP BY [tbl 572 Group Home Analysis].[PT-Patient Group];

SELECT Query1.[PT-Patient Group], Query2.NumberOfPatients, Query1.[Total Acq], Query1.[Total Price], Query1.[# of RX's], Query1.[$ Margin], Query1.[Avg $ per RX], Query1.[Avg Marging per RX]
FROM Query1 INNER JOIN Query2 ON Query1.[PT-Patient Group] = Query2.[PT-Patient Group];

[weekend00]

Schon731,

I am not big on SQL I use the visual tools in Access and I might not be the biggest help but I have done similar things in access. I would make another query and just use the [Patient Group] and a Count of the [Patient Code]. Then I would have another query to put the two together. I did this in the query design mode then changed it to SQL. I hope it helps.


SELECT [tbl 572 Group Home Analysis].[PT-Patient Group], Count([tbl 572 Group Home Analysis].[PT-Patient Code]) AS NumberOfPatients
FROM [tbl 572 Group Home Analysis]
GROUP BY [tbl 572 Group Home Analysis].[PT-Patient Group];

SELECT Query1.[PT-Patient Group], Query2.NumberOfPatients, Query1.[Total Acq], Query1.[Total Price], Query1.[# of RX's], Query1.[$ Margin], Query1.[Avg $ per RX], Query1.[Avg Marging per RX]
FROM Query1 INNER JOIN Query2 ON Query1.[PT-Patient Group] = Query2.[PT-Patient Group];

Above highlighted query will return duplicate count number, giving Schon731's example, it return 4 patient instead of 1.

We need to pick up distinct patient_group and patient at first, then count the number of patient:
SELECT [PT-Patient Group], Count([PT-Patient Code]) AS NumberOfPatients
from (select [PT-Patient Group], [PT-Patient Code] FROM [tbl 572 Group Home Analysis] group by [PT-Patient Group], [PT-Patient Code])
GROUP BY [PT-Patient Group];

[Schon731]

I tried this but because the main file is pulled by the line items (RX's) it counts each line and gives me the same count as the # of RX's.