show if has two specific records

  • Thread starter Thread starter deb
  • Start date Start date
D

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
 
2 issues.

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


KARL DEWEY said:
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.


deb said:
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
Click to expand...
Click to expand...
 
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.


deb said:
2 issues.

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


KARL DEWEY said:
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.


deb said:
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
Click to expand...
Click to expand...
Click to expand...
 
--
deb


KARL DEWEY said:
Why, does not your t040Project have field named KeyMilestonesSubID?
Click to expand...
It can be linked to a table that is used as the combobox selections of the
milestones.
What is the error message?
Click to expand...
Asks for parameter value for XX.KeyMilestonesSubID
Post back your SQL.

--
Build a little, test a little.


deb said:
2 issues.

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


KARL DEWEY said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top