Results 1 to 5 of 5
  1. #1
    binway is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    2

    compare update row with previous row to detect changed data


    Hi Team,
    I have some data that changes and gets an update date whenever a field is changed.
    I am trying to report when a row has changed.
    I can get pretty close if I take out the update date using a group by in conjunction with a dedup query but am not convinced I am getting the correct values for the changed row for
    update date and updatedby as I had to use the "last" function in the group by query.
    Some sample data is below.
    ID Name Update_Date Updated by Status Glob_App_Class Simp_act_ret_date Simp_class Display
    9117 App 2014-02-10 11:12:18 714 Act Inter_Plat Maintain
    9117 App 2014-02-28 17:45:07 118 Act Inter_Plat Strategic Y
    9117 App 2014-03-15 07:02:14 859 InAct Invest Strategic Y
    9117 App 2014-03-15 08:39:03 859 InAct Invest Strategic
    9117 App 2014-03-19 00:51:52 880 Act Dep Strategic Y
    9117 App 2014-03-28 09:15:58 714 Act Dep Retire Y
    9117 App 2014-03-31 12:43:27 118 Act Inter_Plat Deprecated/Retire Y
    113 Bridge 2014-02-20 10:52:36 252 Act Dep Invest
    113 Bridge 2014-02-28 17:44:16 118 Act Dep Strategic Y
    113 Bridge 2014-03-07 01:00:03 system Act Dep Strategic
    113 Bridge 2014-03-07 09:01:16 system InAct Dep Strategic Y
    113 Bridge 2014-03-08 09:01:13 system Act Inter_Plat Strategic/Invest Y

    Currently I have the output as a spreadsheet format but ideally want it in the format below which looks to me like a crosstab but nothing I can do gets the crosstab to look like this.

    ID Name Update_Date Updated by Status Field Old Val New Val
    9117 App 2014-02-28 17:45:07 118 Act Simp_Class Maintain Strategic
    9118 App 2014-03-15 07:02:14 119 InAct Glob_app_Class Inter_Plat Invest
    9117 App 2014-03-19 00:51:52 880 Act Glob_app_Class Invest Dep
    any help appreciated.

  2. #2
    rstruck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    6
    Remember that a table of records is like a deck of cards. The order does not matter until you have to do something.
    to get the table in the correct order so you can compare one row against the previous row, you have to have an Order By ID, Name, Updated By and Updated_Date desc.
    Then you have to loop through the recordset that you created when doing this.
    I see three nested loops. One for the ID, one for the Name and the innermost one by the updated_By

    The first thing you check is if the id, name or update by
    rsUpdate.movelast
    rsupdate.movefirst ' now if you wanted you have a count of how many records you have to loop.
    Do while not rsupdate.eof
    ' get variables to hold the values set them to empty for the first run
    workingid = ""
    workingName = ""
    workingupdatedby = ""
    if workingid = rsupdate!ID then
    if workingingname = rsupdate!name then

    workingid = rsupdate!id
    workingName = rsupdate!name
    workingupdatedby = rsupdate!updatedby
    if
    if

  3. #3
    rstruck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    6
    sorry hit enter.
    if workingid = rsupdate!ID then
    if workingingname = rsupdate!name then
    if workingupdatedby = rsupdate!updatedby then
    now you have to compare the values and then get the column names for the fields that have changed


    workingid = rsupdate!id
    workingName = rsupdate!name
    workingupdatedby = rsupdate!updatedby

  4. #4
    rstruck is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2014
    Posts
    6
    What I would try and do is to add another 2 column to you table called columnfield and oldvalue. and when the form is changed you can get these values right away and populate the table. then it is an easy select statement to get what you want.
    Sorry for adding three posts , too fast typing and not enough reading.

  5. #5
    binway is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    May 2014
    Posts
    2
    Thanks rstruck - I was thinking of using some sort of SQL. I'll see what I can do with the code you have suggested.

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

Similar Threads

  1. Default value doesn't update when date is changed
    By TheHarleygirl2005 in forum Programming
    Replies: 4
    Last Post: 08-30-2013, 05:57 PM
  2. Replies: 4
    Last Post: 11-21-2012, 03:17 PM
  3. How to Compare, Flag, Update two tables?
    By Plan B in forum Queries
    Replies: 3
    Last Post: 04-23-2012, 02:24 PM
  4. Replies: 3
    Last Post: 11-23-2011, 12:25 AM
  5. Replies: 1
    Last Post: 03-20-2011, 05:59 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