Since there are only 11 queries, seems reasonable to look at each of the tables and modify the query(s) accordingly.
If the field involved is not in TableA, then remove the table A reference -similarly for B and C.
You could create some more extensive vba to check if the field was in tableA if so then build the sql, then check tableB, then tableC. But that may be more effort than required.
I see this activity more of vba to create the code that would eventually be executed. More like building a tool to address a specific problem. Just trying to quantify how big the "problem" is.
With 200 fields across multiple tables, you may want adjust your strategy. Sometimes when removing replicates you want more attributes of specific record to identify this field value/record from others.
Can you give us some more info /details re the 200 fields? What tables would these span?
There may be ways to use the data dictionary stuff to simplify some coding, but more info needed.
Update:
Just ran this query to see which table(s) contain each field. Could use this sort of thing to adjust your query definitions.
Code:
SELECT FieldNamesQ.field_name, data_dictionary.table_name
FROM FieldNamesQ INNER JOIN data_dictionary ON
FieldNamesQ.field_name = data_dictionary.field_name
WHERE ((Not (([data_dictionary].[table_name])="data_dictionary" Or ([data_dictionary].[table_name]) Like "LK*")));
Returns:
field_name |
table_name |
Age |
OrgA |
Age |
OrgB |
Age |
OrgC |
Education |
OrgB |
Education |
OrgA |
Email |
OrgA |
FirstName |
OrgB |
FirstName |
OrgA |
Gender |
OrgC |
Gender |
OrgA |
Gender |
OrgB |
LastName |
OrgB |
LastName |
OrgA |
LastName |
OrgC |
MaritalStatus |
OrgA |
NumberOfChildren |
OrgB |
Occupation |
OrgA |
Phone |
OrgA |
Salary |
OrgB |
Almost forgot:
Here is query FieldNamesQ
Code:
SELECT DISTINCT data_dictionary.field_name
FROM data_dictionary
WHERE (((data_dictionary.field_name)<>"ID") AND
((([data_dictionary].[table_name])="data_dictionary" Or
([data_dictionary].[table_name]) Like "LK*")=False));