Get a complete list of unique values from multiple columns in multiple tables

Question

Back story if you want it: I have an odd situation. An organization affiliated with my own provides us with a database that we use heavily; a few other organizations also use it, but our records are easily more than 90% of the total. We have a web frontend for data entry that is connected to the live database, but we only get the backend data as an Access file of selected tables that are sent to us periodically.

That's a hassle in general, but a critical problem that I run into in every report is differentiating records produced by our organization from others'. Records are identified by the staff who created them, but I don't have (and am unlikely to get) the users table itself - which means I have to manually keep a list of which user IDs correspond to which users, and if those users belong to our organization, etc. Right now, I'm building a sort of shadow DB that links to the data extract and has queries that append that kind of information onto the data tables - so when I pull out a list of records, I can get them by user ID, name, organization, role, etc.

The problem: not all users create or modify records of all types, so the user IDs I need to make this list complete are scattered across several tables. How can I create a list of unique user IDs from across all of these tables? I'm currently using a union of the IDs from the two biggest tables, but I don't know if I can stack subquery upon subquery to make that work - and I'm kind of hesitant to dive into writing that for Access without knowing if it will ultimately work. I'm interested in other methods, too.

TL;DR: What's the simplest way to get a column of the unique values of several columns that are spread across several tables?

Thanks in advance for your help!

Answer 1

Combine SELECT queries on each of the tables into a UNION query. The UNION query returns distinct values.

SELECT UserID FROM Table1
UNION
SELECT UserID FROM Table2
UNION
SELECT UserID FROM Table3;