Importing data with autonum conflicts

[magicdust]

Hi there, I have had 4 people doing data entry into 4 separate Acsess dbs.

I now have to merge the data, however alot of the records have the same contact Id which is set by auto numbering.

It wont let me import the data due to conflicts!!

Any help out there please.

I'm a bit of a self taught newbie so be kind please.

Thanks in advance.

B

 

[petrosky]

Hi magicdust,

What I have done in the past...Assuming your table structure is identical, you can use append queries to achieve what you need.

Here is some SQL you can paste into the SQL view of the query window...

INSERT INTO tblContacts ( ContactFName, ContactLName )
SELECT tblNewcontacts.ContactFName, tblNewcontacts.ContactLName
FROM tblContacts INNER JOIN tblNewcontacts ON tblContacts.contactID = tblNewcontacts.ContactID;

This will assign a new contactID to the records appended.

If you need it explained better, please don't hesitate to repost.

Regards,

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[petrosky]

Sorry,

Append query should have looked like this...

INSERT INTO tblContacts ( ContactFName, ContactLName )
SELECT tblNewcontacts.ContactFName, tblNewcontacts.ContactLName
FROM tblNewcontacts;

Remember- It's nice to be important,
but it's important to be nice

 

[magicdust]

Hi , so do I create a special query just for this code...or ?? Create a querie with all the fields I am trying to import and stick the code in there?

Sorry I'm so green, its amazing I have got this far...youre help is appreciated.

Cheers
Magic

 

[petrosky]

Hi again,

Say all 4 tables are called tblContacts. Each table has the same field names/types to be appended. You can't have two tables with the same name in on database container (to my knowledge) so in my example I have renamed one of the tables to tblNewContacts.


Create a new query in design view from the tblNewContacts and drag the fields you want to append (excluding the contactID field) in my example above I was appending ContactFName and ContactLname.

Once you have dragged the fields in question, choose Query, Append Query, Same Database, choose the original tblContacts.

Your records should append with sequential (new) contactID.

It is VERY important to remember when experimenting in this way, that you have a verified backup in a safe location.

Good luck.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[magicdust]

OMG it worked!!!!!!!!!!!!!!!!!!!!!!!

What a genius!!!

Thanks so much.

Blessings!!