Clever Geek Handbook

Syntax error in accessing join operation

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

I don't know why I am getting this error in Access. When I remove the parentheses after "FROM", I get a syntax error in the From clause. Please advise. Thank!

+3


source to share


2 answers


Access does not support OUTER JOIN. You need a variant, which will be LEFT JOIN or RIGHT JOIN with Is Null criteria in a field where no data exists.

Here is Microsoft took over this issue: http://office.microsoft.com/en-gb/access-help/creating-an-outer-join-query-in-access-HA001034555.aspx



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

+3


source


MS Access does not support FULL OUTER JOIN

, but the same can be emulated with UNION

both LEFT JOIN

and RIGHT JOIN

as shown 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];
+1


source






More articles:


All Articles