Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2016
    Posts
    1

    Unanswered: Incrementing numbers using DMax in update query

    Hi there,

    I am Chris, trying to update the SystemID field using DMax command in update query.
    I want to append the records from another table into the main table which has the SystemID field, so I am seeking some help as to what I should put into the query for the SystemID should be updated automatically.

    tblListeUpdatedBlockRoyaltyDetails table contains the records I want to transfer to PtblAllPayments which has the SystemID field which should be incremented with this command DMax("SystemID","PtblAllPayments","SystemID")+1

    The command simply adds 1 to the last maximum ID number and gives the same number to all the records being transferred PtblAllPayments table

    See Attachment for more details
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,862
    Provided Answers: 17
    You could try using the ROW_NUMBER() function to add to the DMax function, instead of a constant + 1. Still, I think this smells of re-inventing the IDENTITY column, and this may just dig you deeper into that hole.

Posting Permissions

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