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!