Help in combining two queries in ACCESS 2010

[louisedp]

Hi All,

Kindly Help me in combining the outputs of two queries.
Query 1:

SELECT t1.[Associate], IIf(t1.[Resolved_Count] Is Null,0,t1.[Resolved_Count]) AS Resolved_Count, IIf(t2.[Backlog_Count] Is Null, 0, t2.[Backlog_Count]) AS Backlog_Count
FROM (SELECT [Remedy Login Name] AS Associate, count(id.[Resolved#USER]) AS Resolved_Count FROM tblAssociateDetail AS ad LEFT JOIN IncDump AS Id ON ad.[Remedy Login Name]=id.[Resolved#USER] WHERE [SubTower] = 'IT_Ops' GROUP BY [Remedy Login Name]) AS T1 LEFT JOIN (SELECT [Remedy Login Name] AS Associate, count(id.[Assignee+]) AS Backlog_Count FROM tblAssociateDetail AS AD LEFT JOIN IncDump AS ID ON AD.[INC Assignee Name]=ID.[Assignee+] WHERE [SubTower] = 'IT_Ops' AND ([Status*]='Assigned' OR [Status*]='In Progress' OR [Status*]='Pending') GROUP BY [Remedy Login Name]) AS T2 ON T1.[Associate] = T2.[Associate];

Output of Query 1:
[TABLE="width: 431"]
<TBODY>[TR]
[TD]Associate</SPAN>[/TD]
[TD]Resolved_Count</SPAN>[/TD]
[TD]Backlog_Count</SPAN>[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]James</SPAN>[/TD]
[TD]16</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[/TR]
[TR]
[TD]Louis</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[/TR]
[TR]
[TD]Xavier</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Query 2:
SELECT [Remedy Login Name] AS Associate, count(CRD.[Change ID*+]) AS CR_Implemented
FROM tblAssociateDetail AS ad LEFT JOIN CRDump AS CRD ON ad.[Remedy Login Name]=CRD.[Change Implementer+] WHERE [SubTower] = 'IT_Ops' GROUP BY [Remedy Login Name];

Output of Query 2:
[TABLE="width: 339"]
<TBODY>[TR]
[TD]Associate</SPAN>[/TD]
[TD]CR_Implemented</SPAN>[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[/TR]
[TR]
[TD]James</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]Louis</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[/TR]
[TR]
[TD]Xavier</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]
Need to merge the above two queries, so that my output would look like below:

[TABLE="width: 547"]
<TBODY>[TR]
[TD]Associate</SPAN>[/TD]
[TD]Resolved_Count</SPAN>[/TD]
[TD]Backlog_Count</SPAN>[/TD]
[TD]CR_Implemented</SPAN>[/TD]
[/TR]
[TR]
[TD]Albert</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[/TR]
[TR]
[TD]James</SPAN>[/TD]
[TD]16</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[TD]2</SPAN>[/TD]
[/TR]
[TR]
[TD]Louis</SPAN>[/TD]
[TD]4</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]3</SPAN>[/TD]
[/TR]
[TR]
[TD]Xavier</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]1</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
Thanks in Advance

 

[healey21]

Just create another query and the common field to join is the Associate Name add the fields from both queries and you have a solution.

 

[louisedp]

Hi Healey,

My Brain was not working until u replied.... Thank you...

Following query is producing the desired output...

SELECT Q1.Associate, Q1.Resolved_Count, Q1.Backlog_Count, Q2.CR_Implemented
FROM
(SELECT t1.[Associate], IIf(t1.[Resolved_Count] Is Null,0,t1.[Resolved_Count]) AS Resolved_Count, IIf(t2.[Backlog_Count] Is Null, 0, t2.[Backlog_Count]) AS Backlog_Count
FROM (SELECT [Remedy Login Name] AS Associate, count(id.[Resolved#USER]) AS Resolved_Count FROM tblAssociateDetail AS ad LEFT JOIN IncDump AS Id ON ad.[Remedy Login Name]=id.[Resolved#USER] WHERE [SubTower] = 'IT_Ops' GROUP BY [Remedy Login Name]) AS T1 LEFT JOIN (SELECT [Remedy Login Name] AS Associate, count(id.[Assignee+]) AS Backlog_Count FROM tblAssociateDetail AS AD LEFT JOIN IncDump AS ID ON AD.[INC Assignee Name]=ID.[Assignee+] WHERE [SubTower] = 'IT_Ops' AND ([Status*]='Assigned' OR [Status*]='In Progress' OR [Status*]='Pending') GROUP BY [Remedy Login Name]) AS T2 ON T1.[Associate] = T2.[Associate]
) AS Q1
LEFT JOIN
(
SELECT ad.[Remedy Login Name] AS Associate, Count(CRD.[Change ID*+]) AS CR_Implemented
FROM tblAssociateDetail AS ad LEFT JOIN CRDump AS CRD ON ad.[Remedy Login Name] = CRD.[Change Implementer+]
WHERE (((ad.[SubTower])='IT_Ops'))
GROUP BY ad.[Remedy Login Name]
) AS Q2
ON Q1.Associate = Q2.Associate

 

[healey21]

Well done for finding a solution and thank you for letting me know it works.