Access reports, combine data from 2 queries
This sounds simple, but I have struggled with this for days now. The answer I want is the % of times a caregiver gives a drug and is overriding getting a second signature. I have one query that can get the caregiver, location, date and and drugs that NEED to have signatures. I get the total of needed signatures by caregiver by location on a report. I have another query that I can get caregiver, location, date and and drugs that were given WITHOUT second signature (overridden). Sub-report can get these totals. But how do I connect the caregiver and location on one report with the other so that I can divide the overridden signatures/ number of drugs needing signature * 100 to get % Overridden?
I want it to look something like this:
Location Nurse Overrides Total Drugs-Need 2nd Sign %Override
Office A
Nurse Jill 3 9 33%
Nurse Tom 5 10 50%
Office B Nurse Bill 0 10 0%
Nurse Mary 2 8 25%
Total 10 37 27%
On top of this, this whole thing is a sub-report on another report. Any help would be appreciated since I have worked many hrs on this and do not have much to show my employer.
I want it to look something like this:
Location Nurse Overrides Total Drugs-Need 2nd Sign %Override
Office A
Nurse Jill 3 9 33%
Nurse Tom 5 10 50%
Office B Nurse Bill 0 10 0%
Nurse Mary 2 8 25%
Total 10 37 27%
On top of this, this whole thing is a sub-report on another report. Any help would be appreciated since I have worked many hrs on this and do not have much to show my employer.
als315
May be you can upload your DB with sample data?
ASKER
I'll try to get some data tomorrow, I have to get up in a few hours and the remote server is not cooperating. Thanks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sounds like you need a 3rd query which uses the two existing queries and links them on location and caregiver. You can then extract all the data for each caregiver.
ASKER
Yes, this is my problem. How do I connect the two queries by location and caregiver? Since they have to be connected by both, I can't do a join on both that says I need all records from the query with signature needed and only those from the other query that match. It says ambiguous join. How do I join them so I can get the totals of both overrides and number of signatures needed? Since there is not a one to one relationship, what do I do? There isn't away to get the totals in the each query then join them is there? I mean, if I have 8 records for Nurse Jill - Location 4 that says she has drugs that need signatures, and 3 records in the other query that says she overrode the signature, how do I connect them so I get Nurse Jill -Location 4 - 3 overrides - 8 drugs needing signatures in one record? I can't do that, so that is my problem. If I have to total on the sub-reports, then how do I connect the data from there?
I'm still working in getting a sample.
Thanks
I'm still working in getting a sample.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I did that I thought. I will post later tonight, I am not at my regular work space and can't get to the data today.
ASKER
I just did a quick look at my data before going out the door and peter57r may have gotten me the query. will post later
May be will be better to delete question or set your own comments as answer? You can reopen this question pressing request attention in the bottom of question.
We don't like to get point if question was not answered.
We don't like to get point if question was not answered.
ASKER
Well, I usually like to give some points if there was an attempt to help. I did not add the question to the database.