Get information from multiple queries without records multiplying

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 :)
LlMaAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
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
 
Patrick MatthewsCommented:
You may not even need to have 4 separate source queries.

Please post the SQL statements for the queries you have now.
0
 
LlMaAuthor Commented:
Peter I have done that and it didn't make any difference :(

Mathew shall do that now
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
LlMaAuthor Commented:
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
 
LlMaAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
LlMaAuthor Commented:
Thanks for your help Peter, it led me to my solution :)

Thanks Patrick :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.