The aitonumbers were autogenreated when new records were added. Common. They
were also visible. But someone decided to rearrange them by retyping them
according to alphabetical order of Lname. I want to put them back in the
original order. So I have to make them temporarily, not primary, not
autonumbers, and replace them according to the Date_Start so it can go back to
the way it was. So forget autonumbering. I will set it back to autonumbering
once I get it back into chronological order of when they were entered. But how
do I update those numbers to do that in the meantime?
I want it to look like the way it was until some idiot changed the ID to be
alphabetical, which of course gets messed up when new records are added and the
autonumbering works chronologically. I wonder if they manually typed in the
numbers or did an update to make the numbers based on last name. Any ideas?
Dear Spam:
First, I'm thinking about what you're using this autonumber to do.
Autonumbers are commonly used to identify rows uniquely internally and
as a unique key on which to base relationships. As such they are
typically not even shown to the user, and are now a basis for sorting
rows. Are you exposing this autonumber to the users?
You suggest that the autonumbers assigned will sort the same as
Date_Start. This would happen only if the user ALWAYS enters data in
Date_Start order. If a row were not added due to some human error,
and then the mistake was caught 2 days later, the autonumber would not
be in Date_Start sequence. So, if you want to see rows in
Date_Start sequence, be sure to order by that, not the autonumber.
Using autonumbers as a supposed sequential numbering of data matching
the order in which the rows are entered has proven to be a dangerous
misunderstanding. What if, on some busy day, you have two users
adding rows. The autonumber sequence will shuffle between the two
users as they each add rows. There are other, more complex problems
with autonumbers.
Next, if you intended for the autonumber to sequence your rows, why
did you ever give anyone the ability to alter the autonumber? Sorry
to sound so critical, but this one really bugs me. Perhaps there's
something about your design I just don't comprehend.
I have a database that started with autonumbers genreating when someone entered
data. Then someone got the bright idea to change them based on last name,
thinking it was necessary to view it by last name. Question is, how do I change
it back based on Date_Start?
I could remove the primary key and turn autonumbers into regular numbers. But
then I have to run something like this on the home table. Anyone know how to
fill in the question marks?
Update Home Set PS_ID = ? Where Date_Start = ?
Or do I have to use ADO and scroll though records based on Date_Start somehow,
populating PS_ID with incremental i's in a for next loop?
(e-mail address removed)
Tom Ellison
Ellison Enterprises - Your One Stop IT Experts
(e-mail address removed)
