Can I 'autonumber' an append query?

  • Thread starter Thread starter Andrew
  • Start date Start date
A

Andrew

Hi, I have a problem with an append query where i need to increment
the number in a field by 1 each time, but the field cannot be set to
autonumber because fixed values are also stored in it. The values
cannot be duplicates, and i cannot change them to anything else
because of the way they are referenced.

I have created a select query that gets the next available number in
the field, but when i run the append query only the first row is
entered - the select query doesn't change to the next available number
when a new row is to be appended.

Is there any way round this so it updates the select query for each
row appended?

Thanks in advance,
Andrew
 
Hi Andrew,

I'm guessing that you are using a subquery to get the max existing ID? If
so, you could probably adjust it to add the rank of each record to the max
existing ID to get the new ID. If you haven't used subqueries to calculate
ranks before, post back with some info on how you are selecting the max ID
and the field names in your append query that could be used to uniquely
identify each record (and thus rank them). You may also want to post the
overall sql of the append query.

HTH, Ted Allen
 
Dear Andrew:

It would be useful to know the purpose of this sequential numbering. There
are several possible solutions, depending on how it is to be used.

Tom Ellison
Microsoft Access MVP
 
Back
Top