Help with Union/Join

[lmg0115]

I am an extreme novice to access however I have taught myself some things thanks to this forum. Can someone please tell me why I keep getting a "Syntax error in join operation" error for the code below? Thanks in advance.

SELECT *
FROM [Test NewProductionRptqryHDR]
LEFT JOIN [Test NewProductionRptqryPR]
ON [Test NewProductionRptqryHDR].HeaderAB = [Test NewProductionRptqryPR].MyField
UNION SELECT *
FROM [Test NewProductionRptqryPR]
RIGHT JOIN [Test NewProductionRptqryPR]
ON [Test NewProductionRptqryHDR].HeaderAB = [Test NewProductionRptqryPR].MyField;

 

[sbenj69]

well, for a union query to work, you have to have the exact number of fields and the same field types.... instead of Select * try selecting the desired columns (fields)

Also, you might want to stay away from having spaces in field names and table names

 

[DCrake]

Simple Software Solutions

To simplify the operation you need to create 2 queries based on your selects give them both the same number of columns and field names Then create your union query based on the 2 queries using the * method.

CodeMaster:

 

[namliam]

SELECT *
FROM      [Test NewProductionRptqryHDR]
LEFT JOIN [Test NewProductionRptqryPR]
ON [Test NewProductionRptqryHDR].HeaderAB = [Test NewProductionRptqryPR].MyField
UNION 
SELECT *
FROM       [Test NewProductionRptqryPR]
RIGHT JOIN [Test NewProductionRptqryPR]
ON [COLOR="red"][B][Test NewProductionRptqryHDR].[/B][/COLOR]HeaderAB = [Test NewProductionRptqryPR].MyField;

Your table [Test NewProductionRptqryHDR] doesnt excist in the second query...

I will spare you the speech on NOT using spaces in query/table names!!!

 

[lmg0115]

Thanks everyone for your responses. I got it to work! YEAH!