Transferring data from table to table.

[jlm722]

I've looked and can't seem to find the answer. I have two db. Each has a table named TblCustomers. I want to get rid of one of the tables and let the databases share one table. So once I enter a customer, they will be automatically on the other database without having to retype all their info in again. Here's where I'm lost. Both tables are identical in design. They both have a customer number that is auto generated, and both have a social security field that, for obvious reasons, is a unique identifier in the table. I need some type of query or something that will take all the customers out of one table and put them in the other. There are several customers who are in both tables, so it needs to skip those.

[dcrake]

You may run into a referential integrity issue here.

Firstly, do both customer tables use an autonumber as their unique primary key?

If so customer ABC Ltd in db 1 may have a PK as 101, whereas in db2 it may be 103. Now if you merge the two tables one set of dependancies will lose out.

If your db's are normalised correctly you would need to ensure that the FK's in related tables that are PK's in the customer tables are sync'ed correctly prior to merging.

Secondly is there or is there scope for customers appearing in both tables that are the same customer but with a variation on the spelling of the name. Thus ABC Ltd and ABC Limited. Which one has the correct version.

Thirdly, you first need to perform a gap analysis to check for possible unmatched records both from 1 into 2 and 2 into 1.

David

[jlm722]

Here is the only difference: db1 has a table called "nextnumber", the customer table in this db gets its customer number from this table. Db2 has an auto number field in its customer table. Db1 has 1,599 customers, db2 has 207. I simply need to put the 207 customers from db2 into db1. There are probably 75 who are the same customer between the two tables. The absolute identical field that I know would not be "keyed in" wrong is the drivers licence number and social security number. Both numbers are set to indexed, no duplicates allowed.

[dcrake]

So by disguising the actual field contents in your question you invoked extra discussion which could have been eliminated in the first instance. Anyway, open the table that has the fewer records and create an append query into the table in the second mdb. When you run this query it will say so many records appended, so many not due to keyy violations or duplicate index values. this will ensure that only unique items are apprearing in the main table.

You can then delete the out of date tabel and link the updated table to the smaller mdb.

David

[jlm722]

Well, I have tried that! It returns the error just as you stated, but the error says it can't copy 207 records due to key errors. Which is every darn record. I am NOT choosing to transfer the "auto number" field, so I can't figure out WHY it's not working. I assure you, all 207 customers are NOT in db1, only a few of them.

[dcrake]

Can you stick both tables into one mdb and post. If sensitive data PM me for details.

See AWF for credatbility rating:

David

[dcrake]

Afterthought:

As a test you could link the small table into the big mdb and then run a unmatched query via the wizard to find records in table 2 that dont exist in table 1.

David