I'm looking to see if there's some code out there I can manipulate, or if I can utilize a model to essentially compare field values between 3 or more tables. The two issues here are
- I have to relate through an intermediate table to go from the Main table to the various tables I want to compare (more below), and
- the string values in the main table aren't exactly the same as the values in the compare tables.
Example: Main_Table has a field Restricted with possible values:
- Restrict 1
- Restrict 2
Primary Key field Main ID relates to a Link_Table with fields: Main_ID, Table_A_ID and Table_B_ID
Table_A_ID and Table_B_ID may or may not be null. If they have values, then they relate to their corresponding compare tables (Table A and Table B).
Table A and B both have Restricted fields. But the values here (strings) are either "X" (meaning restricted) or NULL (meaning unrestricted).
Essentially, I want to compare the Restricted value in Main_Table to the Restricted values of Table A and B. If there are any differences, I want to either select them in Main_Table or output the results in a new table (whatever is easier.)
So, if Main_Table is 'Restrict 1' (or 2), and either Table A or B is NULL, then they fail and that record is selected in Main_Table (or outputted to a new table).
There's quite a bit involved here and I'm not sure if there's already some code out there I can start with.
Update 1 I think screenshots would be confusing, as I'm using generic field and table names here in place of my real data. But I can give you a snapshot of what the tables might look like. I should note...record 101 has a related value in Table_B but not Table_A. I still want to compare Main_Table to Table_B in that case, even though there is no related record in Table_A.