Merging two tables

[jlm722]

I looked and didn't find this information. I have two databases, each has its own table called "customers". Both are identical in design. I have about 300 entries on one of them that I want to transfer to the other db's customer table. What is the correct way to do this?

 

[dcb]

Answer this:
1) Is this a once off requirement?
2) Are there duplicates that you wish to deal with?
2.1) Do you need to assess these on a column level or could one table be assumed as master?

Cheers

 

[jlm722]

I have three databases. All three are split fe/be. Two of them share the "customer" table. So once a customer is entered in one of the databases, you will also have the customer in the other database without having to reenter all their info again. However, the third has never shared that table. So we have many customers who are entered into two different tables. I am going to link this third db so it uses the same table, but I have about 300 customers who need transferred so I don't have to retype all that info. The problem is, there are several who are in both database tables. The unique field is their social security number. So I need some way that it won't duplicate. Ie: if the Ssn is in both, it needs to skip that one.

 

[dcb]

2003 or 2007?

 

[dcb]

If you have 2007 available (supports the "IN" keyword in a query) then:

INSERT INTO Table4 ( SSN, AnotherField )
SELECT Table4a.SSN, Table4a.AnotherField
FROM Table4a
WHERE (((Table4a.SSN) Not In (Select SSN from Table4)));

SSN as PK!!!! mmmm Ok I live in Africa and we have duplicates here! also limits being able to use this for international entities

 

[jlm722]

Access 2000

 

[dcb]

You cant just use the 2007 front end if you have it available? just to run the query - you dont need to change the db's format!

If not the method I would lean to is to code it:
Open the recordset for "Master"
For each record check if it exists in "slave" using SSN
If Not then update slave

Make sense?

 

[jlm722]

What? I have no idea why you are saying I can't use the 2007 front end. I never said anything about 2007. All I have is 2000

 

[jlm722]

Oops pardon me. I was reading your post incorrectly. You were asking if it was possible to use 2007 to resolve my problem. I don't have 2007. There surely is a way to do what I need with 2000

 

[dcb]

If you are going to do this once then instead of writing code can i suggest that you back up both dbs, then in the one you want as the master just append the records to it

INSERT INTO MasterContacts ( SSN, AnotherField )
SELECT ImportContacts.SSN, ImportContacts.AnotherField
FROM ImportContacts;

Access will automatically throw errors on the ones with conflicts caused by the unique SSN (assume this is set as the primary key?) and not import them...

Its a bit dirty but will work - as i said BACKUP

 

[jlm722]

ok, thank you VERY much! I'm assuming that the line you have that says: SELECT ImportContacts.SSN, ImportContacts.ANotherFiled, (do i need to keep adding all other fields across here after a comma?) And where do i do this? in a query? And yes, i only need to do this ONE time. from there on out, after i have them all using the same table, they will be correct from that point on...

 

[dcb]

BACKUP

Yip thats the idea in a query
you have to add the fields in both the colums list - in brackets - and the select list

you can use the query builder for this (Access calls this an append query) or the sql tab
you have to press the !to run the query

If you get stuck just give me the names of your 2 tables and colums and I will write it for you

BACKUP