Help with a Union Query, I think...

M

MeSteve

I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
 
J

John Spencer

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
M

MeSteve

That only returns the records where there is a 7 for MilestoneID. I still
nee records that have a 6 but no 7 to show up also.

John Spencer said:
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
 
M

MeSteve

What is the purpose of the 0 = ... line?

John Spencer said:
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
 
J

John Spencer

Logic error on my part. Try this one.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMileStones.MileStoneId =6
AND tblProjectMileStones.ProjectID NOT IN
(SELECT ProjectID
FROM tblProjectMilestones
WHERE MileStoneID = 7))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What is the purpose of the 0 = ... line?

John Spencer said:
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
Click to expand...
 
M

MeSteve

That works brilliantly, now I need to reverse engineer what you did so I can
understand it.

At the risk of being greedy, could I get it to display "TBD" for all of the
other projects?

John Spencer said:
Logic error on my part. Try this one.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMileStones.MileStoneId =6
AND tblProjectMileStones.ProjectID NOT IN
(SELECT ProjectID
FROM tblProjectMilestones
WHERE MileStoneID = 7))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What is the purpose of the 0 = ... line?

John Spencer said:
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

MeSteve wrote:
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I'm sorry but I don't understand your question. What other projects?


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
That works brilliantly, now I need to reverse engineer what you did so I can
understand it.

At the risk of being greedy, could I get it to display "TBD" for all of the
other projects?

John Spencer said:
Logic error on my part. Try this one.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMileStones.MileStoneId =6
AND tblProjectMileStones.ProjectID NOT IN
(SELECT ProjectID
FROM tblProjectMilestones
WHERE MileStoneID = 7))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What is the purpose of the 0 = ... line?

:

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

MeSteve wrote:
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
Click to expand...
 
M

MeSteve

Oh, sorry. Can I return "TBA" for any record (Project) that doesn't have
either a 6 or 7.

Here is what I have: tblProjects, tblMilestones, tblProjectMilestones.
tblMilestones only gets a record if that particular milestone has been added.
The 6 and 7 you already worked ot for me is the est completion date and the
actual completion date respectively. What I would like is for "TBA" to be
shown if a record doesn't have either of those dates populated yet as a catch
all.

Ultimately, I need every project to show in the list. Thank you for your
help so far.

John Spencer said:
I'm sorry but I don't understand your question. What other projects?


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
That works brilliantly, now I need to reverse engineer what you did so I can
understand it.

At the risk of being greedy, could I get it to display "TBD" for all of the
other projects?

John Spencer said:
Logic error on my part. Try this one.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMileStones.MileStoneId =6
AND tblProjectMileStones.ProjectID NOT IN
(SELECT ProjectID
FROM tblProjectMilestones
WHERE MileStoneID = 7))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


MeSteve wrote:
What is the purpose of the 0 = ... line?

:

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7
OR (tblProjectMilestones.[MilestoneID] = 6 and
0 = (SELECT Count(*) From tblProjectMilestones WHERE MilestoneID = 7))


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

MeSteve wrote:
I have a union query that is doing almost what I need.

SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 6;
UNION
SELECT tbl_Projects.*, tblProjectMilestones.*
FROM tbl_Projects INNER JOIN tblProjectMilestones
ON tbl_Projects.[ProjectID] = tblProjectMilestones.[ProjectID]
WHERE tblProjectMilestones.[MilestoneID] = 7;

What I need to do is include the record where MilestoneID = 7 (if it
exists), if not I want MilestoneID = 6 to show. I only want one or the other
but this is returning both.

Said another way, if there is a record with MilestoneID = 7, don't show the
record where all else is same except MilestoneID = 6.

thanks.
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Stuck on a query expression 12
Simple Question - Driving me Crazy 2
Duplicated results 3
query help 1
And I Cry 7
Subquery help? 3
A query based on another query 2
Query Madness 4

Top