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

x
?
Solved

Get information from multiple queries without records multiplying

Posted on 2011-09-25
7
Medium Priority
?
273 Views
Last Modified: 2012-08-13
Hi Experts, hopefully I will be able to explain this and make sense.

I have a number of queries which each contain data I require, they are all based on the same initial dataset, but, each query calculates different things. So I have

Query 1 which calculates number of inspections
Query 2 which calculates number of inspections complete
Query 3 which calculates number of emergency inspections
Query 4 which calculates number of emergency inspections complete

All queries contain the user id as the identifying field e.g.
Query 1
User ID    Num Inspections
Cane12          412
Brandy34       321

Query 2
User ID    Num Inspections complete
Cane12          389
Brandy34       145

etc....

I want to pull all this data together in a single query so it gives me
End Query
User ID    Num Inspections  Num Inspections complete
Cane12          412                                 389
Brandy34       321                                 145


However, what I am currently getting is:
End Query
User ID    Num Inspections  Num Inspections complete
Cane12          412                                 389
Cane12          412                                 145
Brandy34       321                                 389
Brandy34       321                                 145

Can anyone please help me out with this, I am getting desperate. Please make any explanation fairly simple as I am a very new user. Many thanks in advance :)
0
Comment
Question by:LlMa
  • 4
  • 2
7 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 2000 total points
ID: 36595069
In the query that 'brings it all together' you should be joining all four queries in a chain , using the Userid as the join field from one to the next.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36595075
You may not even need to have 4 separate source queries.

Please post the SQL statements for the queries you have now.
0
 

Author Comment

by:LlMa
ID: 36595084
Peter I have done that and it didn't make any difference :(

Mathew shall do that now
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:LlMa
ID: 36595096
Mathew here is the SQL code

KnownInsp
SELECT qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, Count(qryWeeklyInspectionUpdateSub.inMethod) AS [Known Inspections], qryWeeklyInspectionUpdateSub.Emergency
FROM qryWeeklyInspectionUpdateSub
GROUP BY qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, qryWeeklyInspectionUpdateSub.Emergency
HAVING (((qryWeeklyInspectionUpdateSub.Emergency)=No));

EmergencyInsp
SELECT qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, Count(qryWeeklyInspectionUpdateSub.inMethod) AS [Emergent Inspections], qryWeeklyInspectionUpdateSub.Emergency
FROM qryWeeklyInspectionUpdateSub
GROUP BY qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, qryWeeklyInspectionUpdateSub.Emergency
HAVING (((qryWeeklyInspectionUpdateSub.Emergency)=Yes));

InspComplete – This has to have a small sub query to determine if the inspection is complete as there are a number of different complete statuses
SELECT qryWeeklyUpdateCompInspsSub.Year, qryWeeklyUpdateCompInspsSub.UserId, Sum(qryWeeklyUpdateCompInspsSub.CountOfinMethod) AS [Complete Inspections]
FROM qryWeeklyUpdateCompInspsSub
GROUP BY qryWeeklyUpdateCompInspsSub.Year, qryWeeklyUpdateCompInspsSub.UserId;
Sub for that
SELECT qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, Count(qryWeeklyInspectionUpdateSub.inMethod) AS CountOfinMethod, qryWeeklyInspectionUpdateSub.inStatus
FROM qryWeeklyInspectionUpdateSub
GROUP BY qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, qryWeeklyInspectionUpdateSub.inStatus
HAVING (((qryWeeklyInspectionUpdateSub.inStatus) Like "Insp Comp*"));

EmergencyInspComplete – As before, this has to have a small sub query to determine if the inspection is complete as there are a number of different complete statuses
SELECT qryWeeklyUpdateEmergCompSub.Year, qryWeeklyUpdateEmergCompSub.UserId, Sum(qryWeeklyUpdateEmergCompSub.CountOfinMethod) AS [Complete Emergent Inspections]
FROM qryWeeklyUpdateEmergCompSub
GROUP BY qryWeeklyUpdateEmergCompSub.Year, qryWeeklyUpdateEmergCompSub.UserId;
Sub for that
SELECT qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, Count(qryWeeklyInspectionUpdateSub.inMethod) AS CountOfinMethod, qryWeeklyInspectionUpdateSub.inStatus, qryWeeklyInspectionUpdateSub.Emergency
FROM qryWeeklyInspectionUpdateSub
GROUP BY qryWeeklyInspectionUpdateSub.Year, qryWeeklyInspectionUpdateSub.UserId, qryWeeklyInspectionUpdateSub.inStatus, qryWeeklyInspectionUpdateSub.Emergency
HAVING (((qryWeeklyInspectionUpdateSub.inStatus) Like "Insp Comp*") AND ((qryWeeklyInspectionUpdateSub.Emergency)=Yes));
0
 

Author Comment

by:LlMa
ID: 36595113
I think I have just cracked it - though it would still be nice to have all that in one

I added the user id table and had all four queries linked to that instead and it works :D
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36595592
LlMa,

Glad to hear you got it working.  Your approach is essentially the same thing as what Pete posted in http:#a36595069, so I recommend you select his answer unless the performance using your work-around is unacceptable.

Patrick
0
 

Author Closing Comment

by:LlMa
ID: 36598109
Thanks for your help Peter, it led me to my solution :)

Thanks Patrick :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

834 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