query help

[deb]

I am using access 2003

My query has 3 tables
t040Project (PK ProjectID)
t052OrdNoDetails(PK OrdNoDetailsID FK ProjectID) 1 to Many
t51KeyMilestones(PK KeyMilestonesID FK ProjectID) 1 to Many

I need only one record per ProjectID, that has at least one OrdNoDetailsID
from the t052OrdNoDetails table and must have Both KeyMilestonesSubID 12 and
one that =20 and the KeyMilestonesSubID=12 must show the one with the latest
ActualDt field from t51KeyMilestones table

Show Only one record per ProjectID

from t040Project I need ProjectID and ProjectName

from t052OrdNoDetails - Display record only if there is a OrdNoDetailsID(no
not need to show the field, I just need to make sure the OrdNoDetailsID
exists )

from t51KeyMilestones - Display record only if there is a record for field
KeyMilestonesSubID = 12 AND a record for field KeyMilestonesSubID =20. Only
need to show the field KeyMilestonesSubID = 12 and the ActualDt field ,show
the most recent date in the ActualDt field since there may be multiples

Is that complicated or what...

Thanks,

 

[Daryl S]

Deb -
Try this:

select t040Project.ProjectID, t040Project.ProjectName,
t51KeyMilestones.KeyMilestonesSubID, t51KeyMilestones.ActualDt
from t040Project INNER JOIN t052OrdNoDetails on t040Project.ProjectID =
t052OrdNoDetails.ProjectID
Where t51KeyMilestones.KeyMilestonesSubID = 12
AND exists(select 'X' from t51KeyMilestones where t040Project.ProjectID =
t052OrdNoDetails.ProjectID AND t51KeyMilestones.KeyMilestonesSubID = 20)
AND exists(select 'X' from t052OrdNoDetails where t052OrdNoDetails.ProjectID
= t040Project.ProjectID)