Share via

How to obtain running increment number in query

Anonymous
2022-07-06T18:06:33+00:00

I was using the below sql code to append records from a query to a working table.

Every time I run the below sql code, I use to delete the previous record first and then run the code.

INSERT INTO TblLimit ( VendorNo, FormNo, DoNo, DoDt, ExtPrice )

SELECT LimitCheck1.VendorNo, LimitCheck1.FormNo, LimitCheck1.DoNo, LimitCheck1.DoDt, LimitCheck1.ExtPrice

FROM LimitCheck1;

Now a new Numer Type field "RecoID" has been added in the TblLimit, which should be in sequence of 1,2,3 and so on as per records.

Please advise how to obtain the requirement within the query, the Revised sql code should be as below :

INSERT INTO TblLimit ( RecoID, VendorNo, FormNo, DoNo, DoDt, ExtPrice )

SELECT RunningNumSolutionNeedHERE, LimitCheck1.VendorNo, LimitCheck1.FormNo, LimitCheck1.DoNo, LimitCheck1.DoDt, LimitCheck1.ExtPrice

FROM LimitCheck1;

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2022-07-06T18:57:21+00:00

    Try

    INSERT INTO TblLimit ( RecoID, VendorNo, FormNo, DoNo, DoDt, ExtPrice )

    SELECT Nz(DMax("RecoID","TblLimit),0)+1, LimitCheck1.VendorNo, LimitCheck1.FormNo, LimitCheck1.DoNo, LimitCheck1.DoDt, LimitCheck1.ExtPrice

    FROM LimitCheck1;

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-07-07T15:30:45+00:00

    A good learning website with lot of situation's solution.

    Thanking you for sharing.

    Regards

    0 comments
  2. Duane Hookom 26,820 Reputation points Volunteer Moderator
    2022-07-07T02:02:27+00:00

    The running increment should be based on some order based on values in you table. Can you define that order?

    0 comments