show if has two specific records

[deb]

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

I would like to show ProjectID data if the ProjectID is associated to two
KeyMilestonesSubID's ( 12 and 20)

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
112............XYZ..............2/1/2002.....1.................12
112............XYZ..............3/1/2002.....1.................20

Because it is the only ProjectID that has both 12 and 20 in KeyMilestonesSubID

Thanks in advance

 

[deb]

2 issues.

had to add my t51KeyMilestones table
and
it does not recognize xx
--
deb

Try this --
SELECT ProjectID, ProjectName, ActualDt, UnitNo, KeyMilestonesSubID
FROM t040Project
WHERE ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 12)
AND ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 20);


--
Build a little, test a little.

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

I would like to show ProjectID data if the ProjectID is associated to two
KeyMilestonesSubID's ( 12 and 20)

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
112............XYZ..............2/1/2002.....1.................12
112............XYZ..............3/1/2002.....1.................20

Because it is the only ProjectID that has both 12 and 20 in KeyMilestonesSubID

Thanks in advance

 

[KARL DEWEY]

had to add my t51KeyMilestones table
Why, does not your t040Project have field named KeyMilestonesSubID?
What is the error message?

Post back your SQL.

--
Build a little, test a little.

2 issues.

had to add my t51KeyMilestones table
and
it does not recognize xx
--
deb

Try this --
SELECT ProjectID, ProjectName, ActualDt, UnitNo, KeyMilestonesSubID
FROM t040Project
WHERE ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 12)
AND ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 20);


--
Build a little, test a little.

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

I would like to show ProjectID data if the ProjectID is associated to two
KeyMilestonesSubID's ( 12 and 20)

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
112............XYZ..............2/1/2002.....1.................12
112............XYZ..............3/1/2002.....1.................20

Because it is the only ProjectID that has both 12 and 20 in KeyMilestonesSubID

Thanks in advance

 

[deb]

--
deb

Why, does not your t040Project have field named KeyMilestonesSubID?

It can be linked to a table that is used as the combobox selections of the
milestones.

What is the error message?

Asks for parameter value for XX.KeyMilestonesSubID

Post back your SQL.

--
Build a little, test a little.

2 issues.

had to add my t51KeyMilestones table
and
it does not recognize xx
--
deb

Try this --
SELECT ProjectID, ProjectName, ActualDt, UnitNo, KeyMilestonesSubID
FROM t040Project
WHERE ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 12)
AND ProjectID = (SELECT [XX].ProjectID FROM t040Project AS [XX] WHERE
[XX].ProjectID = [t040Project].ProjectID AND [XX].KeyMilestonesSubID = 20);


--
Build a little, test a little.


:

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

I would like to show ProjectID data if the ProjectID is associated to two
KeyMilestonesSubID's ( 12 and 20)

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
112............XYZ..............2/1/2002.....1.................12
112............XYZ..............3/1/2002.....1.................20

Because it is the only ProjectID that has both 12 and 20 in KeyMilestonesSubID

Thanks in advance