Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    VBA function that will compare a BEFORE/AFTER table (with same record counts) and output any change

    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
    Attached Thumbnails Attached Thumbnails Table1.JPG   Table2.JPG  
    Attached Files Attached Files

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Might be easier for the original conversion routine put out a log file of changes it made.

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Do you have any pointers as to where I might find a framework/template for creating such log file?

  4. #4
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by skydivetom View Post
    Do you have any pointers as to where I might find a framework/template for creating such log file?
    It would have to be tightly integrated with the conversion routine. If the conversion routine currently uses update queries, it might need to change to recordset updates so that the log file could be more easily created. Can you post example code of the conversion routine? That would reveal if the log file is an appropriate approach.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    davegri:

    Attached zip file contains 2 files:
    1. "DB with Notional Business Rules"
    2. "Before_After Comparison"

    Please note the included data AND business rules are completely notional. Instead, please allow me to re-iterate the focus is on the "before" and "after" data comparison.

    Recap of the DB:
    1. It includes 2 sets of tables for "BODY_PART" and "INJURY_TYPE".
    2. The tables containing a "_Backup" suffix are ultimately the "before" tables.
    3. Alternatively, [tbl_BODY_PART] and [tbl_INJURY_TYPE] may be considered the "after" tables.
    4. The module "modUpdateTables" includes the notional business rules which -- when executed -- update some records.
    5. Finally, the form "F01_MainMenu" allows me to reset the "after" tables (i.e., make a copy of the backup and then rename them again).

    Process:
    - Open up the form and click both command buttons "Restore Original Tables" and "Apply Business Rules".
    - Now, for this sample data, I use the attached spreadsheet to identify where a value change took place.
    - That is, in the XLS ("orange" tabs), I copied the records from tables [tbl_BODY_PART] and [tbl_INJURY_TYPE] into columns A:E.
    - Given the values in columns G:K remain unchanged, I use a simple IF statement (in columns M:Q) to determine either "Ok" or "Delta".
    - In this case, all "Delta" values are highlighted in red.

    Process for the VBA:
    - As part of putting together this example, I have included only business rules that focus on the first two (2) fields in each table.
    - In actuality, data for the "Other Field #" may also have business rules that could change a value.
    - Thus, the VBA needs dynamic enough to compare *1st field of 1st table* against *1st field of 2nd table* (i.e., all tables starting with "tbl_") all the way through nth field of Table_Before against nth field of Table_After.
    - As stated in the OP, the table structure for both "before" and "after" tables will be identical... same for the record count. So, just like the Excel, it's comparing A1 against G2 (maybe I should have put the before data onto different tabs vs. columns G:K... but you get the idea).

    Envisioned output from the VBA:
    - Based on the XLS example, all "Delta" (red) values should be returned in the Access query.
    - I understand it will return the entire record so I would get records # 2, 6, 19, and 23 for BODY_PART AND records #7, 8, 11, & 15 for INJURY_TYPE
    - As you know though, the record itself does not tell me which field incurred the value change... so, for starters, if field "Other_1, _2, _3" could be excluded that would be great.
    - However, if the log could actually indicate the "15" was changed to "14", "220" was changed to "100", etc. (1st 2 records) that would be ideal though.

    I hope this further illustrates as to what I'm trying to achieve.

    Thanks,
    Tom
    Attached Files Attached Files

  6. #6
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    I'm curious at what point do you guys start encouraging folks to hire a developer?

  7. #7
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Quote Originally Posted by kd2017 View Post
    I'm curious at what point do you guys start encouraging folks to hire a developer?


    I have to say Tom's posts whilst interesting and extremely well written, recently have become a bit like a blog - "How to create a pretty complex database, without hiring someone".
    @Tom - Please don't take this personally as some responders here seem very keen to assist you, but you don't show much effort to solve these complex problems yourself, and as a result you aren't learning much?

    As a guide to solving your issue, you would need to load two data sets (Existing and New) and step through them comparing the like for like fields.
    Any changes identified would probably be best loaded each changed field into a temp table flagging each record with the changes e.g. record ID, changed field name, old field value, new field value.

    Your current databases have more than enough examples for you to have a go at building this.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  9. #9
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you for the feedback... I appreciate it.

    I found a working solution in another forum.

    https://bytes.com/topic/access/answe...nd-changes-vba

    Again, thank you for the constructive comments and feedback.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Notional Business Rules and Log-davegri-v01.zip

    Attached DB will create 2 log tables during conversion. The changed fields will include asterisks (** 125 **) to call attention. The log tables have suffix _LOG.
    If you want to show only changed records, you can write a select query to search for the asterisks.

    The log files are duplicates of the main files except all numeric fields are programmatically changed to CHAR so the asterisks above could be inserted.
    Code uses a couple of user defined collections to accomplish tasks.

  11. #11
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    Thank you, davegri... I appreciate your sending a version that logs the changes.

    Per my post @ 1354 (Eastern time), I came across a solution in a different forum. While that version had additional objects, I was able to customize it towards my particular project needs with (relatively) minimal amount of code.

    It appears it is very flexible/dynamic and any new field (or new business rule) would automatically be included in the log file. See attached zip that generates the log table.

    Again, I appreciate your help.

    Best,
    Tom
    Attached Files Attached Files

  12. #12
    Minty is online now VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Well that linked solution is exactly what I had envisioned, so good find.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    For info, Tom has now posted a follow up question at UtterAccess.com and at Bytes.com without linking back to this thread
    In fact, also at Access World Forums where he has had a solution done for him
    Last edited by isladogs; 07-29-2021 at 02:12 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    the AWF text looks like a hyperlink but isn't?
    Anyway, your link to AWF from Bytes is OK. I had to log in at AWF just to add EE Help to a list. Yeeuch!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Micron View Post
    the AWF text looks like a hyperlink but isn't?
    Anyway, your link to AWF from Bytes is OK. I had to log in at AWF just to add EE Help to a list. Yeeuch!
    Sorry about the AWF link. I've now fixed it.
    Pity the OP didn't follow the standard crossposting etiquette when posting much the same question to at least 4 forums.
    However, yet again, Tom got full solution(s) provided for him
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 08-09-2020, 04:42 AM
  2. Replies: 1
    Last Post: 08-09-2018, 04:11 PM
  3. Replies: 8
    Last Post: 06-18-2014, 02:51 PM
  4. Checkbox output to text in table record?
    By coronatus in forum Forms
    Replies: 6
    Last Post: 10-27-2012, 08:56 AM
  5. Replies: 2
    Last Post: 12-19-2011, 10:51 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums