none
how to run update query (on dates) in descending order? RRS feed

  • Question


  • I am trying to modify on an old Access 2.0 database before we attempt to upgrade it later down the road.

    I have a simple table with the first field a date/time field. That field is the "Key" field.

    I'm trying to use the query grid in Access 2.0 to run an update query to add 1 day to each record.

    Some of the records won't update due to "key violations"

    I expect that as the query starts with the first record and adds a day to the date... then the same for each successive record... then at some point some of the records are attempting to update to the same date/time as a later record in the table.

    If we could start with the last date/time and add a day... and then add a day to the next-to-last record... we could avoid this issue... if in fact this is the issue.

    The question: How to run an update query starting with the last date/time and continue to update in descending order?

    Thanks for any help.

    Saturday, July 25, 2015 7:42 PM

Answers

  • Your thinking is good - you need to update the records in order starting with the most recent.

    You could try creating a query that sorts the table in descending order by that date/time field, and use that query as input to a second query that performs the update. 

    If you intend to keep using that database you should get it out of 2.0 format as soon as possible. 

    • Marked as answer by Fran_3 Saturday, July 25, 2015 8:19 PM
    Saturday, July 25, 2015 8:09 PM

All replies

  • Your thinking is good - you need to update the records in order starting with the most recent.

    You could try creating a query that sorts the table in descending order by that date/time field, and use that query as input to a second query that performs the update. 

    If you intend to keep using that database you should get it out of 2.0 format as soon as possible. 

    • Marked as answer by Fran_3 Saturday, July 25, 2015 8:19 PM
    Saturday, July 25, 2015 8:09 PM
  • Talus07... great minds think alike... I went back right after posting and the light bulb went off in my head and I did exactly what you suggested and it worked ! :-)

    And, of course your are correct about getting it out of 2.0. We will upgrade it to Access 2000 then to 2013 over the next couple of weeks... and hope the darn thing still works :)

    Thanks for responding to my post.

    Saturday, July 25, 2015 8:19 PM