recreating an autonumber field

  • Thread starter Thread starter Jake Peters
  • Start date Start date
J

Jake Peters

I currently do this so i can link a table with a query that contains the
same values only one record lower relative to the autonumber. So in the
table 1=A, 2=B, 3=C... and in the query 2=A, 3=B, 4=C... etc. I do this in
order to perform some calculations i need (by using a third query to compare
the two, and storing that value in a generated field). The reason i have to
recreate the autonumber is because records get added/deleted. So if i were
to leave it the generated value i use for linking (Autonumber+1) would not
work. This is because Autonumber does not reuse deleted numbers, so the
generated query number can create an unlinkable value. For instance if the
fourth record got deleted from the table:

Table Query
1=A 2=A
2=B 3=B
3=C 4=C
5=E 6=E

The query would still generate a record number of 4, as it is based on the
one "beside" it, but there is no 4 in the table.

My question is, are there any draw backs to recreating an autonumber field?
Or is there a different method of linking or looking up the previous record
and using its value and the current one? Or is all of this alright?


thx
 
Jake said:
I currently do this so i can link a table with a query that contains the
same values only one record lower relative to the autonumber. So in the
table 1=A, 2=B, 3=C... and in the query 2=A, 3=B, 4=C... etc. I do this in
order to perform some calculations i need (by using a third query to compare
the two, and storing that value in a generated field). The reason i have to
recreate the autonumber is because records get added/deleted. So if i were
to leave it the generated value i use for linking (Autonumber+1) would not
work. This is because Autonumber does not reuse deleted numbers, so the
generated query number can create an unlinkable value. For instance if the
fourth record got deleted from the table:

Table Query
1=A 2=A
2=B 3=B
3=C 4=C
5=E 6=E

The query would still generate a record number of 4, as it is based on the
one "beside" it, but there is no 4 in the table.

My question is, are there any draw backs to recreating an autonumber field?
Or is there a different method of linking or looking up the previous record
and using its value and the current one? Or is all of this alright?
Click to expand...


You would never be able to get an autonumber to sync up,
forget that idea.

You can use a subquery to get the "next" number:

SELECT table.somefield,
(Select Min(X.key)
FROM table As X
WHERE X.key > table.key
) As NextKey
FROM table
 
Back
Top