Autonumber problem when joining table data 2

[waldemar]

Another one...

[tblAddresses] contains Addresses of different types: Parents, Students, Invoices... The key index is [intAddressID] being an autonumber field.

Each of the tables [tblParents], [tblStudents] and [tblInvoices] have a simple reference to one intAddressID from [tblAddresses].

While I was working on the database I continously recreated the addresses from the start while somebody else needed to work on the Invoices already. So I have a different [intAddressID] set than the [tblInvoices] from that person refers to (the old version) which I need to join now.

If it would NOT BE an autonumber field, I could simple copy&paste the old Invoice's Addresses into the new [tblAddresses] so the references in [tblInvoices] would be correct. However the Autonumber field does not allow copying the exact [intAddressID] but instead keeps incrementing the ID -> wrong references in [tblInvoices] to [tblAddresses].

Does anybody know a tricky procedure do so anyway? (Changing Autonumber to Number, copy&paste and changing it back to Autonumber doesnt work.)

Thanks
waldemar

 

[zevw]

The way I do it is, changing the value to Number and changing all the numbers to the proper value and then resetting it as an AutoNumber or using an update query by joining the matching fields for example the Name.

I hope this helps;

 

[oharab]

IIRC if you do the updates with an append query, then so long as no duplicates are added, it should take over the unique identifiers as well.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[waldemar]

I'm not sure if I get this right...

1. I deleted all the Invoice Addresses from the tblAddresses
2. I changed the intAddressID to AutoValue to Long Number
3. In inserted the other set of Invoice Adresses, the ID stays - perfect - reference works

How do I get an AutoValue Index from here again? I can not change the Long Number back to AutoValue. I inserted a new AutoValue field intAddressID2 - but how do I get these new Auto-Key-Values into the related Tables (tblInvoices, tblStudents, etc.)?

Regards
waldemar

 

[zevw]

Waldemar!!

The easiest way to do it now is, create a new table with the exact same structure (without data). change those fields to AutoNumber in the new table "before you enter any data". Append all the information from the old Table into the new table, your next number will be the next number after you entered.

This should work 100 %

Just for you to know:
if you have a problem with an auto number Table you can make a new table and append all those records that are giving you problems and delete the from your real table. Go into the new table change the AutoNumber field to a regular number field and make all your changes in that new table. Then append back all those records to your real table and before you append create a new record and put there the latest number you want your autoNumber to start with.

for example:

if you want to start with 654

then your last record in the new table should be 654, your table will start incrementing from there forward.

Beware!! before you contine entering data, run a repair on The Database.

I hope this is clear.

 

[waldemar]

zevw, well of course this is clear. But what about my references in the related table? Creating an all new autonumber index confuses the whole index, no relation to the other table will work anymore since all entries have different index values now...

 

[zevw]

Do you have anything in the second Table that is unique except for AutoNumber!!

If you do, do an update query on the other table and replace all the ID's in the second table with the new numbers. join the 2 tables with what is unique.

If you do not have anything unique!!

Then put all the numbers from your second table into you main table which creates the AutoNumber as stated above, by the the suggestion stated above (creating a new table with a table structure only and appending you data into that table). If you have numbers in middle that are missing because something went messy just create records with numbers only and you will enter data into those fields by scrolling through your table by doing go to the next or previous record.

 

[waldemar]

Ahhh ok, I tried the insert query thing which worked perfect. Thanks guys.

Regards
waldemar