Record Sequence Problem

[Geneman]

I created a simple database with about 10 fields.

I entered an initial 1866 records into that database in order and each got assigned an autonumber.

These records are from a cemetery and are listed in row order left to right.

After completing the initial set of records, I have come upon some 50 records
of individuals who are buried at the cemetery, but have no head stone. Some of these people I know exactly where their record should be located in the sequence and others I do not.

Problem, I have this autonumber record number that so far has controlled the sequence. The new records would end up at the end of that autonumber sequence.

I know that I can delete that autonumber field but I have no other unique field currently to use to sort the records on after I delete that autonumber field.

I do have row number field which will help some, but not a grave number field.
But I can add one. Then do a two column sort I guess to reset the sequence.
Then recreate the autonumber after all the new entries are in place.

Question is, is there any other more simple way to plug these records into
their spots without going through the process I just described?

I also have a couple of records I missed on the original transcription that need to be plugged in.

The row-grave sequence is part of what makes this project unique. I do NOT want to change it to an Alphabetical sort. Yes I know that would simplify things.

[MMcCarthy]

The autonumber uniqueID field should not be used as significant data for this reason. If you keep it purely for indexed sorting rather than trying to order the fields by you will be more successful. The order in which records are stored in a table should not be significant. Particularly in Access where this order can be overwritten in query results.

If you create a Grave Number field and copy over the numbers from the autonumber field then the sequential order of the autonumber field won't matter any more. This field can still be indexed as Yes (No Duplicates) but not be the primary key.

Please let me know if you wish to clarify anything.

Mary

[Geneman]

The autonumber uniqueID field should not be used as significant data for this reason. If you keep it purely for indexed sorting rather than trying to order the fields by you will be more successful. The order in which records are stored in a table should not be significant. Particularly in Access where this order can be overwritten in query results.

If you create a Grave Number field and copy over the numbers from the autonumber field then the sequential order of the autonumber field won't matter any more. This field can still be indexed as Yes (No Duplicates) but not be the primary key.

Please let me know if you wish to clarify anything.

Mary

That's what I ended up doing was create a sequential grave number that can be adjusted as entries are added. I still have to shift numbers but far less than
1866 of them. : )

It has allowed me to plug in all the outside source records without a problem
and thus makes the result much better.
Live and learn.

Thanks for your reply. Please close this thread.