Ordering Nightmare

[rickhubner]

I have a list that I use to replace "Old" IDs with "New IDs my problem is the appropriate ordering of the list so that I don't rename record IDs and then later in the list find that there was already a record there with that and then possibly renaming multiple erroneous IDs to another ID# later on.

I know this is confusing - in essence - please understand there can only be one unique record for each ID.

Perhaps an example would help:

The record in row 2 on the list needs to be changed from 16059 to 16066 but further down list on row 5 the 16059 is being changed to 16056 and on row 21 16056 is being changed to 16053.

So I have to eyeball all three to decide which to do 1st, 2nd & 3rd so as not to create a second record with an existing old ID # and then also perhaps change them both to another ID - if the order of the changes is not considered.



In this example, by eyeballing, I can see that if I change row21 1st, row5 2nd and row2 3rd I will be okay. No doubles created and no records updated incorrectly. I can't find a way to do the proper ordering with Excel or VBA in MS Access. It's driving me nuts !!!

Excuse the "Row" reference, because I'm at my wits end, I had posted this on an Excel forum as well and the "Row" reference seemed helpful there.

No help yet though.

[ranman256]

The IDs are irrelevant to the user. They are something for the pc to join records.
You shouldn't need to change an autonum id, just the foreign key.

Order too is relative. data has no order until you decide what field to sort.
If you are removing duplicates, mark them as such without this renumbering.