SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

No Full Outer Join in Access

Recently I needed to help a colleague with a small inventory true up task. We needed to know items that were in List A and not in List B, and vice versa. It was a universe of about 1000 items with a good sized delta, 15 to 20 percent.

For tools I had the options of Excel and Access installed, or I could look for other options (one would be to export both to txt files and use a diff utility). Being a data guy and not an Excel power user, I figured I’d just import into Access, do a quick full join, and call it a day.

Turns out – and maybe I used to know this – that Access supports left or right joins, but not a full, even if you type in the query instead of using the designer. I know that join isn’t common, but it is handy, and it’s hardly non-standard. It took me a minute longer to stack two queries and get the answer I needed. Close to trivial for me, maybe not so for a not-quite power user, though I guess you could argue that only a power user would try to solve the problem this way.

Problem solved, for now at least, but it sparked a couple interesting thoughts – more on those tomorrow.


I'm Andy Warren, currently a SQL Server trainer with End to End Training. Over the past few years I've been a developer, DBA, and IT Director. I was one of the original founders of SQLServerCentral.com and helped grow that community from zero to about 300k members before deciding to move on to other ventures.


Leave a comment on the original post [www.sqlandy.com, opens in a new window]

Loading comments...