Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: Identifying a change in data.

    Using the expression builder within a select query, I'm trying to return the data record(s) where a change has first occurred in a specific data field. It's probably easier to illustrate with an example..........I need to return any data record(s) for an employee where the there was a change in mgr_id and the date it occurred. There may be multiple effective dates because other data elements on the employee's records have changed.

    empl mgr_id eff_date

    Smith 125 03/15/2004
    Smith 125 07/01/2004
    Smith 309 09/15/2004
    Smith 309 03/01/2005
    Smith 428 07/01/2005
    Green 267 03/15/2004
    Green 267 07/01/2004
    Green 267 01/01/2005
    Green 267 07/01/2005

    The data record(s) I'm trying to return are:

    Smith 309 09/15/2004
    Smith 428 07/01/2005

    I would appreciate any advise. Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could create a recordset and iterate through it to do your comparisons. I don't think you can do this using expression builder. maybe if you have a sequential primary key you could use a DLookup on, but even that is a little shaky. In general, one record in a query has no knowledge of the contents of any other record. It can't do comparisons on the record "above" it.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    A sneakier solution would be to place some code in the forms after update events and examine the relevant controls.oldvalue property against the .value property.

    if there are doscrepancines then write to a separate table, adding any other relevant data eg time taken, computer that changed the data, userid etc... and then use that table to generate you report.

    effectively create a version of an audit log......

  4. #4
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    I've tried capturing/querying actual changes to the data and I think that's going to work. Thanks for the suggestion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •