Counting and Percentages

[akh2010]

Hello!

I am trying to count how many of the "sames" and "differences", as well as calculate the percentages of the number of "sames" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.

Also, I need to categorize these diagnosis by each type of diagnosis.

I would really appreciate any help!

Thanks.

 

[Purdue15]

Assuming everything is in one table you first need to create a design query and use the total function. From there you should select the column that contains "Same" and use the count function and then do the same for "Differences". This will give you a number of how many were admitted

As for as the percentages go you first need to build a query off of the count query you will then use a formula similar to this in the field section

([Same]-[Differences])/[Same]*100)

You then can add the original table to the query and build a one to one relationship to the query connecting diagnosis to each other and then add it to the query to make sure its displayed

To give a more clear answer I would need some more information on how you have everything set up

 

[akh2010]

So.. should I create a calculated field on the table or query that tells me if HospitalAdmitDiagnosis = FacilityAdmitDiagnosis, or if they're not the same? (i.e. output of -1 and 0.) Or should I create two separate columns, one that has 1 for yes and 0 for no, the column headings being "Same" and "Different"?

Additionally, the percentages must be
[Same]/[Same]+[Different]*100

Here's how I have everything set up:
PatientT - Columns as name, FacilityAdmitDiagnosis, HospitalAdmitDiagnosis, HospitalAdmitFromID. The Primary key is "PatientID".

FacilityAdmitDiagnosis and HospitalAdmitDiagnosis are two seperate tables. Columns are like such for both: Primary key is "--DiagnosisID," and the next column is "--Diagnosis". The diagnosis for both are: Pneumonia, Cancer, Ortho.

HospitalAdmitIDT - Primary key as "HospitalAdmitID," and the next column is "HospitalAdmit." There are 3 different hospitals listed under "HospitalAdmit".

 

[Mihail]

Be careful ! Your formula for percentage is not OK.
But this is the last issue for you.

In my thinking your database should be designed like this:
1) You have a list with patients. So you must have a table with patients:
tblPatients (ID_Patient, other fields like PatientName, Birthday etc)
2) You have a list with possible diagnostics, so another table is needed:
tblDiagnostics(ID_Diagnostic, Diagnostic)
3) You have (probably) a list with hospitals, so...
tblHospitals(ID_Hospital, Hospital, maybe other fields with information about each hospital)

Now you must link the Facility diagnostic to Hospital diagnostic for (each) a certain patient:
tblPatientsDiagnostics with this fields:
a) ID_PatientDiagnostic
b) FacilityDiagnostic (lookup on table tblDiagnostics)
c) Hospital (lookup on table tblHospitals)
d) HospitalDiagnostic (lookup on table tblDiagnostics)

This design will allow you to answer to yours questions and not only.
As example: How many times the Facility diagnostic is the same with the diagnostic of a certain Hospital ?

Hope this is a help for you.

 

[akh2010]

I changed the formula for the percentages. Additionally, I changed the columns to do the lookup on other tables like you suggested. Thank you!

How do I get Access to report the same/different diagnosis per hospital? Meaning, several hospitals listed with the breakdown for each hospital. Additionally, can I break it down further and have it list each diagnosis and the count of the ones that are the same... per hospital?

Let me know if you don't understand what I said. It's complicated. :banghead:

 

[Mihail]

I must see your database for more help

 

[akh2010]

This is the database that I'm practicing on. To keep it simple, I eliminated things in the Hospital Data that are irrelevant, such as "FacilityAdmitDate" and "HospitalAdmitDate." Additionally, for patient data, I only included name. The "complete" database will include more information.

 

[Mihail]

Save it in a previous version. I use 2007 so I can not open it

 

[akh2010]

Here's the converted version.

 

[Mihail]

Same Diagnostic:
SELECT HospitalInfoT.PatientID, HospitalInfoT.FacilityAdmitToDX AS AdmitToDX
FROM HospitalInfoT, HospitalInfoT AS HospitalInfoT_1
WHERE (((([HospitalAdmitToDX]=[FacilityAdmitToDX]))=True));

 

[Mihail]

Change True to False (very last word) and must obtain (hope ) not same diagnostic.

 

[akh2010]

Does that go into a query? And if so, for the report I have in my database, if I keep the subform under "Detail" will it help produce the results I want?

Thanks so much for your help so far.

 

[Mihail]

This is yours database. I tweak a little bit the relationships also.

 

[Mihail]

Just a minute. I don't see the report.

 

[akh2010]

This is quite lovely, and I appreciate how you edited my database. I need to figure out how to count the differences and the number of sames, and take percentages next. :'(

I tried figuring it out from the above posted suggestions, but I don't think those suggestions work with the edited database.

 

[Mihail]

Verify this

 

[Mihail]

As you can see... it works. Very well

 

[akh2010]

Wow, that's great! THANK YOU SO VERY MUCH! I've been wracking my brains out for the last couple of weeks trying to figure out how to do this! I really appreciate the help. Now I can go off and finish this project. You're so awesome. Thank you again.

 

[akh2010]

Oh goodness. Mihail, I just realized that I need the percentages per hospital. I'm sorry, I thought this was all figured out. Is there a way to do that? Should I just move the percentages up to Detail on Report?

They also need to be on the same report.. Would I be able to create a new report using the same commands, which will produce the results I need?

Thank you so much for your help so far. I'm sorry to keep asking more questions, I just can't seem to figure this out since it's so complicated to me. :banghead:

 

[Mihail]

Are you sure ?
Better is to ask BEFORE doing something. You can save other "couple of weeks".

Glad to help you.