Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excluding specifc values of a Union Query. 1

Status
Not open for further replies.

CoolFactor

Technical User
Dec 14, 2006
110
US

All I want to do is to exclude [S8] AS SFTE and [S9] AS SFTE
from the Union query

SELECT qry_Smoother2.Location, 'Y0' AS YEAR, [Y0] AS FTE, 'S0' AS S, [S0] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y1' AS YEAR, [Y1] AS FTE, 'S1' AS S, [S1] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y2' AS YEAR, [Y2] AS FTE, 'S2' AS S, [S2] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y3' AS YEAR, [Y3] AS FTE, 'S3' AS S, [S3] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y4' AS YEAR, [Y4] AS FTE, 'S4' AS S, [S4] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y5' AS YEAR, [Y5] AS FTE, 'S5' AS S, [S5] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y6' AS YEAR, [Y6] AS FTE, 'S6' AS S, [S6] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y7' AS YEAR, [Y7] AS FTE, 'S7' AS S, [S7] AS SFTE
FROM qry_Smoother2;
UNION ALL
SELECT qry_Smoother2.Location, 'Y8' AS YEAR, [Y8] AS FTE,'S8' AS S, [S8] AS SFTE
FROM qry_Smoother2;
UNION ALL SELECT qry_Smoother2.Location, 'Y9' AS YEAR, [Y9] AS FTE, 'S9' AS S, [S9] AS SFTE
FROM qry_Smoother2;
 
What do you mean by "exclude"?

You don't want those columns to appear? You want them to be NULL?

If it's the first then you can't do that. Every Select in a union must have the same number of fields.

If it's the second
Code:
[blue]... Preceding Stuff[/blue]
UNION ALL
SELECT qry_Smoother2.Location, 'Y8', [Y8],'S8', NULL 
FROM qry_Smoother2
UNION ALL SELECT qry_Smoother2.Location, 'Y9', [Y9], 'S9', NULL
FROM qry_Smoother2;

UNIONs take their field names from the first SELECT statement. You don't need to repeat the field aliases in the SELECT statements following the first one.

"Year" is a reserved word. It should be in brackets.
 
Golom:

even if you want the null in the first query you can do in
Code:
Select field1 ,field2 [COLOR=red]null as field3[/color]
from table1
union all
Select field1 ,field2  field3
from table2
 
pwise

True enough ... but the OP wanted to eliminate [S8] and [S9] and those are not in the first Select statement.
 
Golem said:
If it's the first then you can't do that. Every Select in a union must have the same number of fields.
I was referring to this line
 
And I was refering to ...

You don't want those columns to appear?

as "the first".

You cannot eliminate columns from some select statements in a UNION query if that would result in a different column count for different SELECT statements.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top