Difference between two tables, unknown fields

Question

Is there a way in Access using SQL to get the difference between 2 tables?

I'm building an audit function and I want to return all records from table1 where a value (or values) doesn't match the corresponding record in table2. Primary keys will always match between the two tables. They will always contain the exact same number of fields, field names, and types, as each other. However, the number and name of those fields cannot be determined before the query is run.

Please also note, I am looking for an Access SQL solution. I know how to solve this with VBA.

Thanks,

Answer 1

Not sure without your table structure but you can probably get that done using NOT IN operator (OR) using WHERE NOT EXISTS like

select * from table1
where some_field not in (select some_other_field from table2);

(OR)

select * from table1 t1
where not exists (select 1 from table2 where some_other_field = t1.some_field);

Answer 2

There are several possibilities to compare fields with known names, but there is no way in SQL to access fields without knowing their name. Mostly becase SQL doesn't consider fields to have a specific order in a table. So the only way to accomplish what you need in pure Access-SQL would be, if there was a SQL-Command for it (kind of like the * as placeholder for all fields). But there isn't. Microsoft Access SQL Reference.

What you COULD do is create an SQL-clause on the fly in VBA. (I know, you said you didn't want to do it in VBA - but this is doing it in SQL, but using VBA to create the SQL..). Doing everything in VBA would probably take some time, but creating an SQL on the fly is very fast and you can optimize it to the specific table. Then executing the SQL is the fastest solution you can get.

Answer 3

SELECT A.*, B.* FROM A FULL JOIN B ON (A.C = B.C) WHERE A.C IS NULL OR B.C IS NULL;

IF you have tables A and B, both with colum C, here are the records, which are present in table A but not in B.To get all the differences with a single query, a full join must be used,like above