Experts:
I need some assistance with (potentially) coding a VBA which compares 2 tables and then dynamically identifies any changes (either via a query or table output). Allow me to provide more details in bullet format below.
Recap:
a. Attached is a DB which contains 4 tables: [tbl_Demo_1a_Before], [tbl_Demo_1b_After], [tbl_Other_2a_Before], [Table_2b_After]
b. tbl_Demo_1a and _1b are identical in their setup (both fields AND # of records); same applies to tbl_Other_2a and _2b.
c. As part of an update routine, however, business rules (i.e., record updates) are applied to various fields in the "_b_After" tables.
d. I want to be able to readily identify which record was updated (or not updated).
e. Ultimately, based on d., I will use the "before/after comparison" to validate if a business "kicked in (or not).
More info:
- Attached JPG illustrate the scenarios where I arbitrarily changed a few values in the "after" tables.
- The data itself is completely notional and purely made up.
- Please keep in mind that some tables may contain more than 50 fields (vs. only the few fields I listed in this example).
- Also, please note that I have more than [tbl_Demo] & [tbl_Other] tables; that is, in reality I have 10 tables (and their associated 'After' copy).
- Point is though the yellow highlighted fields (here shown in Excel column O:T and I:K) depict that "a change" (i.e., "delta") took place.
Process:
- Fields may be added or removed from either "Before" table.
- Once a field has been inserted into the "Before" tables and the new record set is loaded, I then make a copy of the "Before" tables and rename the copy "After".
- At that point, I run the "business rules" which are applied to only the "After" tables.
VBA Requirements:
- The VBA code must be dynamic so that it scans through all tables having, e.g., a prefix = "tbl_".
- Also, the VBA must be "smart" scan through all fields in the existing "tbl_" tables and then compare values for [tbl_Demo_1a_Before].[Firstname] against [tbl_Demo_1b_After].[Firstname]... and every field thereafter.
- If there is no change for any of the records in a particular field, this field does NOT need to be added to the query. Thus, I really only want to show those fields where *at least 1* (or more) records where changed.
- So, in the case for the "Demo" tables, the query would include fields: [FirstName], [Age], [Phone] and [Occupation]. For this data set, there was no change in field [Education], so this field should NOT be included in the query.
- Furthermore, I only want to show records where a change took place. In this case, only records where ID = 1, 4, 7, 8, 14, and 23 (again, this is for the "Demo" tables). Similarly, same rules apply to the "Other" tables.
Challenge:
- Based on what I listed in the VBA requirements, I would expect to get a 4x6 matrix (4 fields and 6 records).
- Here's the issue though... while such query would give me a 4x6 query (much easier to view than a 6x23 matrix), I would NOT necessarily know that that a change occurred for [Age] in record #1 OR [Occupation] for record #7.
- So, not sure if there's a way to actually output the specific change... 'just like the yellow-fields in the Excel spreadsheet.
All that said, I'm open to any approach which would allow me to streamline this validation process.
Thank you in advance for your help!
Tom