Excluding specifc values of a Union Query. 1

[CoolFactor]

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;

 

[Golom]

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

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

 

[PWise]

Golom:

even if you want the null in the first query you can do in

Select field1 ,field2 [COLOR=red]null as field3[/color]
from table1
union all
Select field1 ,field2  field3
from table2

 

[Golom]

pwise

True enough ... but the OP wanted to eliminate [S8] and [S9] and those are not in the first Select statement.

 

[PWise]

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

 

[Golom]

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.

 

[CoolFactor]

Thanks Guys all your suggestions worked out!