Can't figure out query

D

deb

Need help with a query...
Tables -
t040Project (pk-ProjectID)
1-Many
t51KeyMilestones - (pk-KeyMilestoneID)

I would like to show all KeyMilestone data if KeyMilestonesSubID = 12 or is
Null for each ProjectID
and if there is no record for KeyMilestonesSubID =12 then include the
records for KeyMilestonesSubID =20.

Below is my current code...
SELECT DISTINCT t040Project.ProjectID, t040Project.ProjectName,
t51KeyMilestones.ActualDt, t51KeyMilestones.UnitNo,
t51KeyMilestones.KeyMilestonesSubID
FROM t040Project LEFT JOIN t51KeyMilestones ON t040Project.ProjectID =
t51KeyMilestones.ProjectID
GROUP BY t040Project.ProjectID, t040Project.ProjectName,
t51KeyMilestones.ActualDt, t51KeyMilestones.UnitNo,
t51KeyMilestones.KeyMilestonesSubID
HAVING (((t51KeyMilestones.KeyMilestonesSubID)=12 Or
(t51KeyMilestones.KeyMilestonesSubID)=20 Or
(t51KeyMilestones.KeyMilestonesSubID) Is Null))
ORDER BY t040Project.ProjectID DESC;

This currently displays all KeyMilestonesSubID for 12, 20 and Is Null.
How can I edit this to show all KeyMilestonesSubID for 12 and Is Null and
only show KeyMilestonesSubID for 20 if there is no KeyMilestonesSubID 12 for
the ProjectID.

example data.
ProjectID ProjectName ActualDt UnitNo KeyMilestonesSubID
111............ABC..............1/1/2001.....1.................12
112............XYZ..............2/1/2002.....1.................12
112............XYZ..............3/1/2002.....1.................20
113............RST..............3/1/2002.....0.................
114............LMN..............5/2/2001.....1................20

Query to show.
ProjectID ProjectName ActualDt UnitNo KeyMilestonesSubID
111............ABC..............1/1/2001.....1.................12
112............XYZ..............2/1/2002.....1.................12
113............RST..............3/1/2002.....0.................
114............LMN..............5/2/2001.....1................20

Thanks in advance
deb
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top