Help with MS ACCESS Query

It seems to me that i can't figure out simple thing.
I have two tables

table 1 - tblMilestones ( PKMilestoneID-number, ProjectID, MilestoneDate,MilestoneTypeID)
table2 - tblTypesOfMilestones(PKMilestoneTypeID,MilestoneName)

they have one to many relationships based on milestonetypeid.

I need to show The oldest Milestone per project.
But query should output ProjectId, MilestoneName and MilestoneDate

thanks for help.
db-question1.accdb
maximyshkaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
try this


SELECT A.Project_ID, A.MilestoneName,A.Milestone_date
From
(SELECT T1.Project_Id, T2.MilestoneName, T1.Milestone_Date
FROM tblTypesOfMilestones as T2 INNER JOIN tblMilestone as T1 ON T2.Milestonetypeid = T1.Milestonetypeid
) As A
Inner Join
(SELECT T.Project_Id, Min(T.Milestone_Date) AS MinOfMilestone_Date
FROM tblMilestone T
GROUP BY T.Project_Id) as B
On A.Milestone_date=B.MinOfMilestone_Date and A.Project_ID=B.Project_ID
0
 
Michel_NialonCommented:
Hello
please try something like this :

select  
   tbl1.ProjectID,
   tbl1.MilestoneDate,
   tbl2.MilestoneName
from
   tblMilestones tbl1,
   tblTypesOfMilestones tbl2
where
   tbl2.PKMilestoneTypeID = tbl1.MilestoneTypeID
   and tbl1.MilestoneDate = (select min(MilestoneDate ) from tblMilestones )

I have nothing to test it here, but it should work
michel
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.