General solution for missing sequence numbers
Oh my! Please put the gun down and don't go postal. Scientists should be
held responsible for their own stupid actions.
Peter Danes wrote:
Hello John.
The gaps got there in the first place because the owner of the database is a
scientist, not a computer tech. As I explained in response to David's post,
she was using the record number that appears in a form's navigation text box
as an identifier, and trying to keep that synchronized with an autonumber
field. After discovering that a deleted record, even at the end, did not
re-use the next available number (the standard autonumber lament of
beginners) she went at it by erasing text from the individual fields and
entering new text without actually deleting the record. But by the time she
discovered this kludge, she already had gaps in the numbering. I had quite a
time convincing her that the record number in the navigation box is not an
identifier and that her whole approach to this was not the best way to
handle the numbering issue.
As far as the problem of references to specific numbers in other places than
the database, you're right, but it's not something I can address. This is
the way their system works, they like it that way and want it left alone.
And in this case, I don't believe it's really a problem. Most of the gaps
are places where something was written down incorrectly, inadvertently
erased and the old autonumber field wouldn't let them use the number again.
Or some numbers were not used at all - there was a conversion somewhere
along the line, trying to get the record number back in sync with autonumber
and someone managed to sling in a gap of 230 unused numbers. I'm not sure
quite how they managed that, when I asked about it and what they had done,
the general response was that they didn't know what they had done, it just
somehow turned out that way. Again, these are scientists, not computer
techs. In any case, this dataset is what I got handed and I had to do what I
could to accomodate their needs. A better identifying scheme is certainly
not difficult to imagine, but I can't really expect them to drop what
they're doing and go renumber their entire collection just to suit my
technical preferences.
|