Union Query Errors (1 Viewer)

[amunafc]

Dear Gurus
Can someone help me fix the error I am getting when running my query I have attached? Please download it and run the 'Union Query'Query1

Regards

Amuna

 

[boblarson]

Your problem is the double quotes

UNION ALL SELECT [B][COLOR=red]“”,“”,“”,“”,“”, [/COLOR][/B][All Data].[Vote], [All Data].[Type], [All Data].[Sub-Type], [All Data].[Cost Centre], [All Data].[Project], [All Data].[Program], [All Data].[Sub-Program], [All Data].[Sub-Sub-Program], [All Data].[Item], [All Data].[Sub-Item], [B][COLOR=red]“”,“”,“”,“”,“”,“”[/COLOR][/B]

That's not how a Union Query is built. You would need to do this:

SELECT [Everything together].[Estimate], [Everything together].[Y-2_Exp], [Everything together].[Y-1_Approved], [Everything together].[Y-1_Revised], [Everything together].[Y-1_Expenditure], [Everything together].[Vote], [Everything together].[Type], [Everything together].[Sub-Type], [Everything together].[Cost Centre], [Everything together].[Project], [Everything together].[Program], [Everything together].[Sub-Program], [Everything together].[Sub-Sub-Program], [Everything together].[Item], [Everything together].[Sub-Item], [Everything together].[Output1Plan_Y-1], [Everything together].[Output1Prel_Y-1], [Everything together].[Output1Planned_Y0], [Everything together].[Y+1_Projection], [Everything together].[Y+2_Projection], [Everything together].[Total Est Cost]
FROM [Everything together]
 
UNION ALL SELECT Null As Estimate,Null As [Y-2_Exp],Null As [Y-1_Approved],Null As [Y-1_Revised],Null As [Y-1_Expenditure], [All Data].[Vote], [All Data].[Type], [All Data].[Sub-Type], [All Data].[Cost Centre], [All Data].[Project], [All Data].[Program], [All Data].[Sub-Program], [All Data].[Sub-Sub-Program], [All Data].[Item], [All Data].[Sub-Item], Null As [Output1Plan_Y-1],Null As [Output1Prel_Y-1],Null As [Output1Planned_Y0],Null As [Y+1_Projection],Null As [Y+2_Projection],Null As [Total Est Cost]
FROM [All Data];

 

[boblarson]

Oh, and for the future - avoid using special characters (underscore is okay but the rest no) and don't use spaces in object names or field names. It will greatly simplify things for you if you follow that advice.

 

[amunafc]

boblarson

Hey! that was wonderful, thank you a million times. I have four more tables to be added and I will follow the rules.

 

[boblarson]

boblarson

Hey! that was wonderful, thank you a million times. I have four more tables to be added and I will follow the rules.

Well, just so you know - you could - for the second and above queries that are in the Union Query, just use

Null As Expr1, Null As Expr2...etc.

because the field names come from the very first one and the rest just need to have the same number of fields.

 

[amunafc]

Thank again, I managed to fix all the problems. I am now a hero because of your assistance

 

[Galaxiom]

for the second and above queries that are in the Union Query, just use

Null As Expr1, Null As Expr2...etc.

In fact the subsequent subqueries don't need any field names at all. The first subquery sets the names and the rest are assumed.

Null, Null, etc