Counting and Percentages
- Thread starter akh2010
- Start date
@_@ Okay.
I need an all hospital report. For each hospital, I need a count of how many people who came to our facility returned to the hospital with the same diagnosis. Same for the number of people who returned to the hospital with a different diagnosis.
Then, I need a percentage of the number of people with the same diagnosis to both our facility and the hospital. Following is a percentage of the number of people with a different diagnosis to our facility and the hospital. Both of these percentages are for the people who returned to the hospital in <30 days. The percentage is made up of the total amount of patients who returned to the hospital from our facility, including those who returned to the hospital in over 30 days.
I hope that makes sense and is thorough enough.
I need an all hospital report. For each hospital, I need a count of how many people who came to our facility returned to the hospital with the same diagnosis. Same for the number of people who returned to the hospital with a different diagnosis.
Then, I need a percentage of the number of people with the same diagnosis to both our facility and the hospital. Following is a percentage of the number of people with a different diagnosis to our facility and the hospital. Both of these percentages are for the people who returned to the hospital in <30 days. The percentage is made up of the total amount of patients who returned to the hospital from our facility, including those who returned to the hospital in over 30 days.
I hope that makes sense and is thorough enough.
I've attached the database with the date updates. In SameDiagnosis and DifferentDiagnosis, I've added the <30 days. DDQ and SDQ does a count for each hospital and diagnosis, and in HospitalR, I have it broken down like so. The thing I can't figure out is the percents!
As you can see, the new requests need ANOTHER database.
It is why I always say:
BEFORE to start the design answer to this:
1) At what questions the database must answer ?
2) What is the minimal set of input data in order to answer to this questions ?
3) How manually deal with this set of input data in order to answer to the questions ?
So, take a look to this (new) design and say me if it is OK.
Have you more data (or tables) to add ?
Is it OK a relation 1-1 between FacilityEvents and HospitalDiagnostic ? Using other words, are you sure (but SURE, with no doubt) that a patient with a diagnostic from the Facility, do NOT consult more than one (and ONLY one) hospital ?
Waiting for answers before proceed.
It is why I always say:
BEFORE to start the design answer to this:
1) At what questions the database must answer ?
2) What is the minimal set of input data in order to answer to this questions ?
3) How manually deal with this set of input data in order to answer to the questions ?
So, take a look to this (new) design and say me if it is OK.
Have you more data (or tables) to add ?
Is it OK a relation 1-1 between FacilityEvents and HospitalDiagnostic ? Using other words, are you sure (but SURE, with no doubt) that a patient with a diagnostic from the Facility, do NOT consult more than one (and ONLY one) hospital ?
Waiting for answers before proceed.
For the Hospital Transfer report, the only information that I did not add/tell you about was the number of people who transferred to our facility from hospital x. I know how to do this. This is the only thing I need to add.
After speaking with my supervisor, it appears we only track the first hospital the patient consults.
I just want to mention that they are called "events" because several patients may be entered more than once. For example, if Jane went to the hospital twice in 2011, and once in 2013.
Also, I had no idea you could have one "Diagnostic" table and have it applied to several IDs!
Thank you so much for your assistance so far. I really appreciate the time and effort you have put into helping me. I am learning much from you about access databases.
After speaking with my supervisor, it appears we only track the first hospital the patient consults.
I just want to mention that they are called "events" because several patients may be entered more than once. For example, if Jane went to the hospital twice in 2011, and once in 2013.
Also, I had no idea you could have one "Diagnostic" table and have it applied to several IDs!
Thank you so much for your assistance so far. I really appreciate the time and effort you have put into helping me. I am learning much from you about access databases.
THANK YOU SO MUCH!! I'm sure it's less than 30 days.
Is there a way to have the report not show the ones over 30 days, yet include it into the percentage reporting? In query, I put <30 in the criteria for Days Delay, but I'm not sure how to correct the percentage on the report. In query, I also did a count on Patient_ID and tried [txtDiagnosisTotalPatients]/[CountOfID_Patient] but it outurned 100%.
Thank you so very much for helping me out in the last couple of days. Your help means a lot to me. >w<
Is there a way to have the report not show the ones over 30 days, yet include it into the percentage reporting? In query, I put <30 in the criteria for Days Delay, but I'm not sure how to correct the percentage on the report. In query, I also did a count on Patient_ID and tried [txtDiagnosisTotalPatients]/[CountOfID_Patient] but it outurned 100%.
Thank you so very much for helping me out in the last couple of days. Your help means a lot to me. >w<
I'm sorry, I didn't mean to damage the query. I wanted to try to change it myself so I wouldn't have to ask you anymore. 
You have done so much in helping me, I wanted to try to help myself where I could.
My facility needs to have the report not show the ones that are more than 30 days. I'm not sure why, but it's their requirement for the report.
You have done so much in helping me, I wanted to try to help myself where I could. My facility needs to have the report not show the ones that are more than 30 days. I'm not sure why, but it's their requirement for the report.
Ok. I'll give you (hope today) a report (a new one) that will not show this.
I strongly advice to keep what you already have for the case they will change their minds.
And I think that they will do that because a simple count will uncover their lie.
Remain in stand by.
PS
I have joked about the damage of the query. Is YOURS, not my, so you can do anything with it.
Hope you keep a copy of the original
I strongly advice to keep what you already have for the case they will change their minds.
And I think that they will do that because a simple count will uncover their lie.
Remain in stand by.
PS
I have joked about the damage of the query. Is YOURS, not my, so you can do anything with it.
Hope you keep a copy of the original
Ok. This "new" approach will allow you to show 3 times, the same report, with different data (filters).
If yo haven't need for all three buttons, DO NOT remove the unnecessary buttons. Just hide it (set the visibilities to No). If "they" will change their minds, all you have to do is to make the proper button visible.
Good luck !
If yo haven't need for all three buttons, DO NOT remove the unnecessary buttons. Just hide it (set the visibilities to No). If "they" will change their minds, all you have to do is to make the proper button visible.
Good luck !
Thank you so much!
I'm working on the "actual" database now, and I'm using the one that you have graciously built for me as a reference on how to build mine.
If possible, could you explain to me how you calculated this percentage?
=[txtDiagnosticTotalPatientsInTime]/[txtFacilityTotalPatients]
I tried doing the same calculation, replacing "DiagnosticTotalPatientsInTime" with the terminology I am using in this "new" database," inputing:
=[txtReturnduetoAdmissionDX]/[txtTransfersToFacility]
Return due to Admission DX and Transfers to Facility are both my facility's terminology. I am getting the [txtReturnduetoAdmissionDX] using the same IIf function as you (with words replaced)
=IIf([SameDX],"Return due to Admission DX","Return due to Different DX")
You have been a great help to me so far, so if you are unable to continue helping me, I understand. Thank you again for the help so far, and sorry to keep asking you questions.
I'm working on the "actual" database now, and I'm using the one that you have graciously built for me as a reference on how to build mine.
If possible, could you explain to me how you calculated this percentage?
=[txtDiagnosticTotalPatientsInTime]/[txtFacilityTotalPatients]
I tried doing the same calculation, replacing "DiagnosticTotalPatientsInTime" with the terminology I am using in this "new" database," inputing:
=[txtReturnduetoAdmissionDX]/[txtTransfersToFacility]
Return due to Admission DX and Transfers to Facility are both my facility's terminology. I am getting the [txtReturnduetoAdmissionDX] using the same IIf function as you (with words replaced)
=IIf([SameDX],"Return due to Admission DX","Return due to Different DX")
You have been a great help to me so far, so if you are unable to continue helping me, I understand. Thank you again for the help so far, and sorry to keep asking you questions.
You are welcome anytime.
If you will change my names will become hard (or very hard) to help you in the future.
Seems that you know a lot more English than me, so should be a lot easier for you to adapt to my terminology than for me to adapt to yours.
My advice is to NOT modify my names for control names. Change only the labels captions to fit your needs (and your English )
By formatting the control. Take a look at the Format (for that text box) in the Property Sheet.
If you will change my names will become hard (or very hard) to help you in the future.
Seems that you know a lot more English than me, so should be a lot easier for you to adapt to my terminology than for me to adapt to yours.
My advice is to NOT modify my names for control names. Change only the labels captions to fit your needs (and your English
)I think that your question is how this formula show the percentage.
By formatting the control. Take a look at the Format (for that text box) in the Property Sheet.
Thanks for letting me know how to do that! I was able to successfully build a report that could tell me how many people (just count, no names) from a hospital returned to the hospital due to same/different admission, and the percentages!
I'm having difficulty with DayDelay however. I want the report to give an overall count of how many people at this hospital returned to the hospital in less than 30 days. And then a percentage using the txtDiagnosticTotalPatients. I copy and pasted the calculation you used:
=[txtDiagnosticTotalPatientsInTime]/[txtDiagnosticTotalPatients]
=IIf([DayDelay]<=30,"Total patient(s) ""in time""","Total patient(s) NOT ""in time""")
=IIf([DayDelay]<=30,Count(*)*Abs([DayDelay]<=30),Count(*)*Abs([DayDelay]>
Though it's returning the information I want, I get each person as a separate entry. Meaning, for Jane, Erin, and Kyle, I get a separate count and percentage for each (ex: Jane is in time, gets a count of 1, and is considered 33.33%).
Is there a way to make it so I get an overall count? Meaning, for a hospital, the number of people who returned to the hospital <30 days is 3, with a percentage of 100%?
I currently have the above data in a "DayDelay Header" and I have tried putting it in the "HospitalTransfer Header" and the "SameDiagnostic Header." When I do, even for the ones that are over 30 days, it categorizes it as under 30 days and says 100%.
Thank you so much for the help!
I'm having difficulty with DayDelay however. I want the report to give an overall count of how many people at this hospital returned to the hospital in less than 30 days. And then a percentage using the txtDiagnosticTotalPatients. I copy and pasted the calculation you used:
=[txtDiagnosticTotalPatientsInTime]/[txtDiagnosticTotalPatients]
=IIf([DayDelay]<=30,"Total patient(s) ""in time""","Total patient(s) NOT ""in time""")
=IIf([DayDelay]<=30,Count(*)*Abs([DayDelay]<=30),Count(*)*Abs([DayDelay]>
Though it's returning the information I want, I get each person as a separate entry. Meaning, for Jane, Erin, and Kyle, I get a separate count and percentage for each (ex: Jane is in time, gets a count of 1, and is considered 33.33%).
Is there a way to make it so I get an overall count? Meaning, for a hospital, the number of people who returned to the hospital <30 days is 3, with a percentage of 100%?
I currently have the above data in a "DayDelay Header" and I have tried putting it in the "HospitalTransfer Header" and the "SameDiagnostic Header." When I do, even for the ones that are over 30 days, it categorizes it as under 30 days and says 100%.
Thank you so much for the help!
I further realized another issue. I am needing percentages of people who returned to the hospital for the same/different admission... under 30 days. I am able to turn out percentages in the reports for all people, not those under 30 days specifically. Is there a way to do that, other than put <30 days in the query? Putting in <30 in the query would be problematic because it would not give me the correct numbers, then.
Thanks.
Thanks.
I have asked you if you have said to me all the requirements and your answer has been YES.
Seems that yo have not read the blue text in post #25.
On the other hand, my English not allow me to understand every word from you.
So, if you need more help you must say something like that:
1) I wish to know how many patient with cancer diagnostic are going to THIS hospital?
or
2) What is the percent from total patients with cancer.
Very simple words and very specific problems.
Seems that yo have not read the blue text in post #25.
On the other hand, my English not allow me to understand every word from you.
So, if you need more help you must say something like that:
1) I wish to know how many patient with cancer diagnostic are going to THIS hospital?
or
2) What is the percent from total patients with cancer.
Very simple words and very specific problems.
I'm sorry, I thought I covered all the bases for the report. :banghead:
For DaysDelay, I need it to calculate a total percent for each hospital for: number of people who returned to hospital in less than 30 days. The way I'm doing it now is returning data for each person who went to each hospital, not an overall total.
Additionally, is there a way to get percentages of return to hospital for same/different diagnosis for people who were under 30 days?
For DaysDelay, I need it to calculate a total percent for each hospital for: number of people who returned to hospital in less than 30 days. The way I'm doing it now is returning data for each person who went to each hospital, not an overall total.
Additionally, is there a way to get percentages of return to hospital for same/different diagnosis for people who were under 30 days?
Similar threads
Solved
DCOUNT
