Percentages Query

[mabrown81]

I have a patient registry database in which I am developing EBC Score Reports. They are basically based on percentages. I have a patient table and a providers table. I have created a query PatientCountByProvider that shows me how many patients each provider has. What I want is to create a query that take certain percentages. For example. I have a Condition Met query that shows me all of the patients that meet a certain criteria. What I want to know is how many patients of each provider's total are on it. SO say Provider 1 has 100 patients which I found from my patientcount query.. and has 20 patients on the condition query, how do I create a query that gives me that 20/100 so show the percentage. Any help would be GREATLY appreciated. I'm losr in the water here... Thanks

[rpeare]

If they are two different queries join the queries on the PROVIDER ID, then divide your patient count on your CONDITION query by your patient count on your PATIENT COUNT query.

[rpeare]

NOTE: you could likely do this in a single query depending on your data structure but since you've already got the elements set up it'll be easier to use what you have.

[mabrown81]

If they are two different queries join the queries on the PROVIDER ID, then divide your patient count on your CONDITION query by your patient count on your PATIENT COUNT query.

That worked like a charm. Now my only issue is this: I want multiple columns with percentages but I know that queries are sort of a process of elmination, so when if a certain provider doesnt meet the criteria for all columns included then they are left off. Is there any way I can simply list ALL my providers and then show the percentages for the ones that it applies to? For example, say I have Prov1 who has 3/6 patients who are asthma so thats 50% and Prov2 has no asthma patients but has 1/4 on diabetes patients which 25%. If i choose both of these in the query, i will come back with nothing.

[rpeare]

are you looking for a patient count threshhold, a percentage threshhold or a patients with asthma (count) threshold?

using your example you have something like:

Provider  AsthmaCt  PatCt  PctAsthma
Prov1     3         6      50%
Prov2     1         4      25%

What is your desired end result

To show both of the providers but only show a percentage if they are above a certain level? something like (assuming your threshhold is 30% asthma patients):

Provider  AsthmaCt  PatCt  PctAsthma
Prov1     3         6      50%
Prov2     1         4

or to only show providers who meet a certain threshold (again assuming a 30% threshold)

Provider  AsthmaCt  PatCt  PctAsthma
Prov1     3         6      50%

[mabrown81]

Great question: See I have multiple tables. I have a diabetes table and an asthma table. Prov1 may have 30 patients total with 10 of them being asthma. Prov2 may have no asthma patients at all, but has 5 diabetes patients with a total of 1- patients, Prov1 may have diabetes patients also. So my query would have Provider, asthmact, patCt, PctAsthma, Diabct, PatCt, PctDiabets. if i include this in the query, only Prov1 will show up which makes sense. I actually want a form I guess it is that will show me the different counts and percentages without using process of elimination.

[rpeare]

I don't understand. If you are not putting criteria in your query it should be showing you everything (depending on if you're using left join/right join or inner join) if your query is all inner joins it's only going to show you items where the linked fields are equal. I suspect it has to do with how your joins are set up.

You likely do not want to use inner joins (if the lines between your tables/queries have no arrow at either end). If you double click the lines it will show you options, if the PROVIDER is the driving force you want that arrow pointing FROM your provider TO your asthma/diabetes/etc sub queries/tables. Without an example of your data structure and query setup I can't really give you anything better to go on.

[mabrown81]

That was it!!! I clicked on the arrow and had it show me all providers regardless of! Thanks so much because I was dead in the water.

[mabrown81]

One more question, how do I get the #Error to just show as zero? I know its because zero/zero in which a provider has zero of each condition.

[rpeare]

it depends on whether your denominator is zero or null
something like this would handle both:

iif(isnull([denominatorfield]) or [denominatorfield] = 0, 0, <percentage formula>)

[mabrown81]

it depends on whether your denominator is zero or null
something like this would handle both:

iif(isnull([denominatorfield]) or [denominatorfield] = 0, 0, <percentage formula>)

This is my query and some do have zero for denominator just not sure where to enter the info that you gave. I apologize for being such a novice.
Expr1: [ChildWellVisitCount]![CountOfFirst Name]/[ChildAdolescentPatientCount]![CountOfPatientID]

[rpeare]

Expr1: [ChildWellVisitCount]![CountOfFirst Name]/[ChildAdolescentPatientCount]![CountOfPatientID]

This is your Numerator:

[ChildWellVisitCount]![CountOfFirst Name]

This is your Denominator:

[ChildAdolescentPatientCount]![CountOfPatientID]

This is your Percentage Formula:

[ChildWellVisitCount]![CountOfFirst Name]/[ChildAdolescentPatientCount]![CountOfPatientID]

just substitute into the formula I gave you.

[mabrown81]

iif(isnull([ChildWellVisitCount]![CountOfFirst Name]) or [[ChildWellVisitCount]![CountOfFirst Name]] = 0, 0, <[ChildWellVisitCount]![CountOfFirst Name]/[ChildAdolescentPatientCount]![CountOfPatientID]>) so this is my formula for the field? Thanks again for your help.