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
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