Trouble Joining Two Queries

[ianclark2992]

Hey Everyone,

New to the forums here and I have a question. I am trying to create a report which requires me to pull the following data into a query: client name, audit name, audit status, lead manager on audit, the most recent milestone per engagement, and the milestone start and due dates. This information is found in two seperate tables

I have written two separate queries that pull the required information from each table. However, I am having trouble joining these two queries into one without encountering an error.



Query for Potential Pilot Engagements:

SELECT pe.[Client Name], pe.[Audit Name], pe.[Status], pe.[IA DA Team Manager]
FROM
[Potential Pilot Engagements] pe

Query for Pilot Milestones:

SELECT pm.eid, pm.Milestone, pm.Status, pm.[Start Date], pm.[Due Date]
FROM [Pilot Milestone] pm
INNER JOIN
(
SELECT EID, MAX(ID) AS Current
FROM [Pilot Milestone]
GROUP BY EID
)
groupedpm ON pm.EID = groupedpm.EID AND pm.id = groupedpm.Current

If anyone knows how I can resolve this issue it would be greatly appreciated.

Thanks!

[June7]

Try including AS after the nested query

) AS groupedpm ON

[ianclark2992]

In case anyone here works on a similar query in the future I received the following help from a fellow employee

SELECT [Potential Pilot Engagements].[Client Name], [Potential Pilot Engagements].[Audit Name], [Potential Pilot Engagements].[IA DA team Manager], [Potential Pilot Engagements].Status, pm.Milestone, pm.[Start Date], pm.[Due Date], pm.Status
FROM [Potential Pilot Engagements]
INNER JOIN
([Pilot Milestone] AS pm
INNER JOIN
(SELECT EID, MIN(MID) AS [Current]
FROM
[Pilot Milestone]
WHERE Status<>"Completed"
GROUP BY EID) AS groupedpm
ON (pm.EID=groupedpm.EID) AND (pm.mid=groupedpm.Current))
ON [Potential Pilot Engagements].EID=pm.EID;