Results 1 to 9 of 9
  1. #1
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568

    How to check which id was changed from previous table?

    Hi Guys,



    in attachment there is sample access database.
    I want to get all ids which was changed or updated.

    So output for update should be:
    ID, Column (based on current table - i mean here that new columns can be added).
    And what is new value for it.

    How to acheve this?

    Best,
    Jacek
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    why would IDs change? These normally are permanent.

  3. #3
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi ranman256,

    as you can see in table current i have:

    Click image for larger version. 

Name:	Screenshot_1.png 
Views:	17 
Size:	57.2 KB 
ID:	34394

    and in previous table i have:

    Click image for larger version. 

Name:	Screenshot_2.png 
Views:	17 
Size:	36.5 KB 
ID:	34393

    So ID 1 was updated.
    In previous version i had "Paul Jurdys" and in current version i have "Paul",
    what sql statement should i write to see : ID = 1 and Name = Paul (where changes were made).

    Best,
    Jacek

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jacek,
    Have a look at the two "changed" queries in the updated file. One is using ORs and the other is a union query.
    Cheers,
    Vlad

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, forgot the file....
    Attached Files Attached Files

  6. #6
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    thank you Vlad,

    very good solution.
    Problem is that somehow i have to create automation for it.

    To avoid manually write Name and all column names.
    And furthermore this queries i have to write in Excel using ODBC object so i think that this will work only within 1 sql statement.

    something like here:

    https://stackoverflow.com/questions/...th-differences

    I think that it is not possible i can have simple solution:

    - have ID, and column names from Current table and only these ones where changes were made.
    So In this case we will ahve 2 outputs:
    ID = 1, Name = Paul, System = Windows 10,
    ID = 2, Name = Peter Gladosz, System = Linux 4

    Best,
    Jacek

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Jacek,

    Have a look at the updated file - open query1 and it will create the union query for you (qryCompareTables) - it calls the VBA custom function vcCompareTable.

    Cheers,
    Vlad
    Attached Files Attached Files

  8. #8
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    hi Vlad,

    thank you, wow why you are so smart ?

    but still this is not what i mean, i think.
    I should have one sql statement for this but maybe this is not possible without code.

    Best,
    Jacek

  9. #9
    jaryszek is offline Expert
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    568
    Hi Vlad,

    i have one query to achevie this:


    Code:
    SELECT 
        CURRENT.ORDER
        ,CURRENT.System AS [System NewValue]
        ,Previous.System AS [System OldValue]
        ,CURRENT.Name AS [Name NewValue]
        ,Previous.Name AS [Name OldValue]
     
    FROM 
        [CURRENT] 
        INNER JOIN Previous ON CURRENT.ORDER = Previous.ORDER 
                                                  AND ( 
                                                         CURRENT.System<>[Previous].[System]
                                                         OR
                                                         CURRENT.Name<>[Previous].[Name]
                                                         )
    Topic is solved,

    Best,
    Jacek

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Deselect check box in previous records
    By matt704 in forum Forms
    Replies: 24
    Last Post: 03-06-2017, 10:19 AM
  2. Replies: 2
    Last Post: 12-10-2015, 09:24 AM
  3. protecting previous data from being changed
    By LethalMeatball in forum Security
    Replies: 4
    Last Post: 07-26-2014, 02:58 PM
  4. Replies: 4
    Last Post: 05-14-2014, 10:03 PM
  5. VBA to check if Data Has Changed
    By jo15765 in forum Programming
    Replies: 2
    Last Post: 11-14-2013, 03:09 PM

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