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:

Associate</SPAN>	Resolved_Count</SPAN>	Backlog_Count</SPAN>
Albert</SPAN>	0</SPAN>	2</SPAN>
James</SPAN>	16</SPAN>	1</SPAN>
Louis</SPAN>	4</SPAN>	0</SPAN>
Xavier</SPAN>	0</SPAN>	0</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

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:

Associate</SPAN>	CR_Implemented</SPAN>
Albert</SPAN>	0</SPAN>
James</SPAN>	2</SPAN>
Louis</SPAN>	3</SPAN>
Xavier</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

Need to merge the above two queries, so that my output would look like below:

Associate</SPAN>	Resolved_Count</SPAN>	Backlog_Count</SPAN>	CR_Implemented</SPAN>
Albert</SPAN>	0</SPAN>	2</SPAN>	0</SPAN>
James</SPAN>	16</SPAN>	1</SPAN>	2</SPAN>
Louis</SPAN>	4</SPAN>	0</SPAN>	3</SPAN>
Xavier</SPAN>	0</SPAN>	0</SPAN>	1</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

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.