Merging two tables.

[Chigger Hill]

I have two databases that do essentially the same thing. Database 1 has its
main table linked to a very large database. Database 2 has its main table
linked to a different large database. These two databases contain
essentially the same information (but not entirely) with different primary
keys. They are lists of employees with different employee numbers for each
table. Is there an easy way of merging these two tables into one database
with a different primary key (assigned)?

I do not use code at all.

 

[Golfinray]

Could you just import one table into the other database? Try File/get
external data/import. Then go in and assign the keys.

 

[Chigger Hill]

I have the same employees in both databases with different primary keys for
the same employee in each table. I have employees in one table and not the
other. I need to keep the tables linked because of keeping the data current.
The database that these are linked to are the active employees of two sister
companies that do work in each company.

 

[Piet Linden]

I have the same employees in both databases with different primary keys for
the same employee in each table.  I have employees in one table and notthe
other.  I need to keep the tables linked because of keeping the data current.
 The database that these are linked to are the active employees of two sister
companies that do work in each company.

The part here that worries me is the "very large database" part. You
theoretically can create a union query that returns all records a
table in the very large DB and the other large db. The problem is
that the performance will *not* be good. At least that way you can
query the tables... I would probably filter the two tables _first_ and
then union those results together - that way you can use the indexing
on the tables. Other than that, I am out of ideas...

 

[JimS]

Couple of issues...

You need to be able to identify the members of each table and be able to
somehow "know" they are the same...SSN, or Last Name, or something. That
becomes a key on which you do not allow duplicates...let's say it's SSN.

Using table1, create a make-table query that creates a new table which
enforces SSN as an index with no duplicates allowed. You don't need code for
this. Then make the table and observe you have no duplicates....

Using table2, create an append query to the combined table that loads up
table2 again observing any duplicate records. You could use the "find
duplicates" wizard to find them, then be sure the duplicate count matches
when you run the append query.

If you need info from table2 other than what was in table1 for the duplicate
records, then you can run an update query to fill in the blanks in the
combined table. Then abandon table1 and table2.

If I understand your requirement, this should meet it....

 

[Chigger Hill]

Thanks for the info. I have been working along the same lines but went
around the world to get next door. I will continue to refine my solution
using your input.