Auto Numbering

[Ed]

I would like to create a field that can hold the value of
its respective record. For example, a field
named "RecordNum" would equal 3 if its position amongst
the other records was the third record.

One requirement is in the event I need to delete a
record. For example, if there are five records and the
fourth record gets deleted, the "RecordNum" field of the
fifth record would change from 5 to 4, automatically
(since it would move from the fifth position to the
fourth).

I think I can do this with some "klunky" programming, but
I thought I would ask if anyone knows an easier way.
Thanks.

-Ed

 

[Adrian Jansen]

Ask yourself what defines the record order. When you can answer that, you
will see what to do.

--
Regards,

Adrian Jansen
J & K MicroSystems
Microcomputer solutions for industrial control

 

[Ben]

Ed-
Why do you want to store a record's relative position? In a RDBMS, a table
is, by definition, an unordered collection of entities. I am currently
working with a legacy system that uses such a scheme, and it causes all
sorts of headaches. If your thinking of this to record, say *when* a record
was added relative to other records, you would be better off including a
timestamp column so you could compare the date/time of records added
relative to each other.
The autonumber column in Access won't do what you wish effectively. Its sole
purpose is to provide unquie values to records in a table. If you delete a
record, that's fine, there's a hole. Access would have to renumber the
entire table from the point of deletion on, as well as every foriegn key
column. This would be a big performance problem (among other things).
Remember that in a database the position of a record in a table is NOT
relevant, and cannot be counted on. When you need to see things in a certian
order, use a query or view.

HTH,
Ben

 

[Duane Hookom]

Is there a good reason why you want to do all of this? Do you need this
presented in a form that allows editing? If the records get sorted in a
different order, do you want them to be re-numbered?

 

[Duane Hookom]

You have sold me on this being a good idea. However, you can create a
calculated value in a query using the expression:
RecNumCount("*","tblJobs","[JobNum]<=" & [JobNum])