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.