I'm trying to UNION ALL several tables from an MS Access DB into CR 2013 using SQL and I keep in running into errors. I don't have any issues with this syntax when coming from an Oracle DB (and I'm very much a newbie) so I'm assuming it's an Access thing (OLE DB ADO connection) where I'm just not knowledgeable on the correct syntax

Anyway, here's the sql and it would be great if somebody can give me some advice. Thanks!

SELECT *

FROM (SELECT invoice_header.invoice_date AS "date", tank_mix_chemicals.gallons_used AS "amount", 'Invoice' AS "type", brand.brand 

  FROM invoice_header

       LEFT OUTER JOIN invoice_tank_mix ON invoice_header.invoice_header_seq = invoice_tank_mix.invoice_header_seq

       LEFT OUTER JOIN tank_mix_chemicals ON tank_mix_chemicals.invoice_detail_seq = invoice_tank_mix.invoice_detail_seq

       LEFT OUTER JOIN brand ON brand.brand_seq = tank_mix_chemicals.brand_seq

       )

UNION ALL

SELECT *

FROM (SELECT transaction_header.transaction_date AS "date", transaction_detail.gallons AS "amount", transaction_type.transaction_type AS 



"type", brand.brand

  FROM transaction_header

  LEFT OUTER JOIN transaction_detail ON transaction_header.transaction_header_seq = transaction_detail.transaction_header_seq

  LEFT OUTER JOIN employee ON transaction_header.employee_seq = employee.employee_seq

  LEFT OUTER JOIN agency ON transaction_header.agency_seq = agency.agency_seq

  LEFT OUTER JOIN transaction_detail ON transaction_detail.transaction_type_seq = transaction_type.transaction_type_seq

  )

Here's the error that I'm getting
[IMG]https://dl.dropboxusercontent.com/u/15099922/Presentation1.png" />

Edited by MattK - 13 Jan 2015 at 7:36am

I can't see the error, and Access is not my forte, does Access really use the "" around a fieldname, SQL Server would usually use []...

just a thought