Union Query Returning Error

[marion34s]

Hello All,
I am working on a query that is using a UNION to join two other queries. I have another successful UNION based query in this db which returns no errors and looks almost identical to this query except for the SELECTed fields. I am receiving the following error when I run it through a checker online:

You have an error in your SQL syntax; it seems the error is around: '[All Recorded by Job].ProjectID, [All Recorded by Job].[Project Name], [All ' at line 2

Despite my best efforts I have not been able to figure out what I'm doing wrong here - any help would be much appreciated.

SELECT
[All Recorded by Job].ProjectID,
[All Recorded by Job].[Project Name],
[All Recorded by Job].Client,
[All Estimates by Job].[SumofBid Hours],
[All Recorded by Job].[SumofRecorded Hours],
NZ ([All Estimates by Job].[SumofBid Hours])-NZ ([All Recorded by Job].[SumofRecorded Hours]) AS [TotalHours Difference],
[All Estimates by Job].[SumOfBid Cost] AS [TotalEstimated Cost],
[All Recorded by Job].[JTD Cost] AS [TotalCost to Date],
NZ ([All Estimates by Job].[SumOfBid Cost])-NZ ([All Recorded by Job].[JTD Cost]) AS [TotalCost Difference],
[All Recorded by Job].[JTD Billed],
NZ ([All Recorded by Job].[JTD Billed])-NZ ([All Recorded by Job].[JTD Cost]) AS [Over/Under Billed],
[All Recorded by Job].[Project Status],
[All Recorded by Job].[PM]

FROM
[All Recorded by Job] LEFT JOIN [All Estimates by Job] ON ([All Recorded by Job].ProjectID=[All Estimates by Job].ProjectID)
UNION SELECT
[All Estimates by Job].ProjectID,
[All Estimates by Job].[Project Name],
[All Estimates by Job].Client,
[All Estimates by Job].[SumofBid Hours],
[All Recorded by Job].[SumofRecorded Hours],
NZ ([All Estimates by Job].[SumofBid Hours])-NZ ([All Recorded by Job].[SumofRecorded Hours]) AS [TotalHours Difference],
[All Estimates by Job].[SumOfBid Cost] AS [TotalEstimated Cost],
[All Recorded by Job].[JTD Cost] AS [TotalCost to Date],
NZ ([All Estimates by Job].[SumOfBid Cost])-NZ ([All Recorded by Job].[JTD Cost]) AS [TotalCost Difference],
[All Recorded by Job].[JTD Billed],
NZ ([All Recorded by Job].[JTD Billed])-NZ ([All Recorded by Job].[JTD Cost]) AS [Over/Under Billed],
[All Estimates by Job].[Project Status],
[All Estimates by Job].[PM]
FROM
[All Recorded by Job] LEFT JOIN [All Estimates by Job] ON ([All Recorded by Job].ProjectID=[All Estimates by Job].ProjectID)
WHERE [All Estimates by Job].ProjectID IS NOT Null

 

[MajP]

Seems would be a whole lot simpler to just make two queries first. The union would be select * from qryone union select * from qrytwo

 

[marion34s]

Seems would be a whole lot simpler to just make two queries first. The union would be select * from qryone union select * from qrytwo

Thanks MajP. It is already two queries

 

[plog]

Seems would be a whole lot simpler to just make two queries first.

First, this exactly. UNION queries should be extremely simple as MajP described--2 SELECTs and 2 FROMs. Any criteria or manipulation (e.g. Nz(), math) that only 1 needs you do prior in a query and save it then use that query in the UNION. Any criteria or manipulation both need you do in another query after you save the UNION.

Second, why? UNIONs are generally hacks:

...
FROM
[All Recorded by Job] LEFT JOIN [All Estimates by Job] ON ([All Recorded by Job].ProjectID=[All Estimates by Job].ProjectID)
...
FROM
[All Recorded by Job] LEFT JOIN [All Estimates by Job] ON ([All Recorded by Job].ProjectID=[All Estimates by Job].ProjectID)
WHERE [All Estimates by Job].ProjectID IS NOT Null

The first query should give you everything you want. It will include records where [All Estimates by Job].ProjectID IS NOT NULL. The second query of the UNION isn't doing anything.

 

[CJ_London]

I am receiving the following error when I run it through a checker online:

and what error do you get when you actually run it?

Although well away from where the checker says there is an error, you should not have a space after the nz function - access would normally remove it. If manually created, access will ignore it - but it might be confusing your checker

 

[marion34s]

Thank you all for your suggestions - I have no explanation but without any changes, the error has disappeared...