I'm using the following SQL statement to find the next highest
autonumber value in a table where "CDUGActID is the autonumber field
in the "CDUGActuals" table:
SELECT CDUGActuals.CDUGActID, CDUGActuals.ActualTonnes, [CDUGActID]+1
AS NextID
FROM CDUGActuals;
This works well as long as no autonumber values have been skipped due
to deletions or cancelled entries. Is there a simple change I can
make so that the "NextID" field will show the next highest autonumber
value irrespective of if autonumber values have been skipped?
I thought of using a dedicated numbering field and populating it
sequentially using the Dmax function, but realized that this would not
be suitable because I may need to apply this query to a filtered list
from the table in which case both the autonumber field and the
dedicated numbering field would not be sequential.