[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access reports, combine data from 2 queries

Posted on 2011-10-10
10
Medium Priority
?
173 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:LindaOKSTATE
  • 5
  • 3
  • 2
10 Comments
 
LVL 40

Expert Comment

by:als315
ID: 36946739
May be you can upload your DB with sample data?
0
 

Author Comment

by:LindaOKSTATE
ID: 36946853
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.
0
 
LVL 40

Assisted Solution

by:als315
als315 earned 400 total points
ID: 36946928
We need only some sample records, which can be used in report, your queries, report and forms used.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 77

Expert Comment

by:peter57r
ID: 36947105
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.
0
 

Author Comment

by:LindaOKSTATE
ID: 36947910
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
0
 
LVL 77

Accepted Solution

by:
peter57r earned 1600 total points
ID: 36948003
You have to click each join line and set the same condition on each one.

Post the sql of both of your first two queries if you are uncertain.
0
 

Author Comment

by:LindaOKSTATE
ID: 36948071
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.
0
 

Author Comment

by:LindaOKSTATE
ID: 36948133
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
0
 
LVL 40

Expert Comment

by:als315
ID: 36957722
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.
0
 

Author Comment

by:LindaOKSTATE
ID: 36957832
Well, I usually like to give some points if there was an attempt to help.  I did not add the question to the database.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month19 days, 14 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question