syntax error in join operation access

3
votes
SELECT *
FROM 
(table1 FULL OUTER JOIN [FY14 PLEDGE_TOTAL]
ON table1.[Id] = [FY14 PLEDGE_TOTAL].[SID]);

I don't know why I'm getting this error on Access. When I remove the parentheses after 'FROM', I get syntax error on From clause. Please advise. Thanks!

2
sqlms-access

2 Answers

3
votes

Access does not support OUTER JOIN. You need the variant which would be LEFT JOIN or RIGHT JOIN with an Is Null criteria on the field where the data do not exist.

Here is Microsoft's take on the issue : http://office.microsoft.com/en-gb/access-help/creating-an-outer-join-query-in-access-HA001034555.aspx

Or something much helpful : http://www.databasejournal.com/features/msaccess/article.php/3516561/Implementing-the-Equivalent-of-a-FULL-OUTER-JOIN-in-Microsoft-Access.htm

1
votes

MS Access don't have support for FULL OUTER JOIN but the same can be emulated using UNION of both a LEFT JOIN and RIGHT JOIN like below

SELECT *
FROM table1 t1 
LEFT JOIN [FY14 PLEDGE_TOTAL] fpt 
ON t1.[Id] = fpt.[SID]

UNION

SELECT *
FROM table1 t2 
RIGHT JOIN [FY14 PLEDGE_TOTAL] fpt1 
ON t2.[Id] = fpt1.[SID];