Problems appending / importing tables

[choaspes]

Dear All

My database is currently in use in three offices and each office has its own discrete copy. I now wish to combine the three databases so that all the offices have access to the same data - and in order to do so I must append all of the table data together, and one particular (the most important) table is causing me grief.

The problem is that there are gaps in the autonumbering on the primary key where records have been deleted, and the primary key for each record is referenced elsewhere - for example in a "previous record ID for this customer" field in a subsequent record in the same table (which is probably bad database practice but it's about 5000 records too late to reconsider that idea). Appending the table together therefore isn't working because Access removes the gaps in the autonumbering when it appends and then the "previous record ID for this customer" references the wrong record.

By exporting to Excel and appending the tables there I have managed to get the table precisely the way I want it (with gaps in the primary key sequence, and all of the related references matching up), but when I attempt to import it back into Access I can't set my carefully edited "primary key" field back to autonumber because there's data already in it.

Am I on a hiding to nothing here? Clearly this isn't an optimal database design, but this isn't my day job and I've only got until Monday to pull this all together...

 

[Isskint]

hi choaspes

This is a real minefield. I have encountered similar needs before and i result to creating a new "master" table to generate new AutoIDs {PK}, then use queries to change the old {PK} to the new {PK}. Depending on the number of fields and the number of tables that have that {PK} as an {FK}, it can take a LONG time.

The first thing you need to realise, the original {PK} is essentially useless.
If you only use the {PK} within the same or one other table, it will be fairly easy, but if you use that {PK} in multiple tables, get the coffee on:banghead:
What about duplicate data? Thats gonna need alcohol
You seem to have done the hard bit on excel so the 'easier' part can be duplicated.
Create a new table in the database (are you using a brand new database or using on of the 3 as the new master??) mirroring the original table. Change the {PK}field to a normal number field (long Integer) and Add an extra field "NewAutoID" as AutoNumber field {PK}. Now you need to paste/import your spreadsheet. This will cause the NewTable to generate new {PK} for each record. You then need to create a query to update each field that uses the old {PK} with the new {PK}.

If you can upload some or all of the spreadsheet indicating which columns reference the {PK}, i will try to help further.

 

[choaspes]

Thank you very much for the response Isskint, it was very helpful indeed. Sorry for not replying sooner - I ended up attempting to conserve my energy for today, and it's given me some time to think about what you said.

In terms of fields referencing the old PK I've got off pretty lightly I think - there's the "ID of previous record within this table for this customer" field but other than that the PK is only reference in a couple of newer tables that have barely any records in yet. I think I should be able to come up with an appropriate update query for "ID of previous record within this table for this customer" this morning.

You're right though it's duplicate records that are going to be the real pain, off the top of my head I'd estimate there will be between one and two hundred in the main table and a few dozen in related tables - job one today will be to write queries to identify them.

Thanks for reassuring me there's nothing simple I've missed an dproviding me with a plan of attack, I am most grateful