Merging Two Identical Databases (Access 2000 format)

[bryan]

I have two Databases that have the same exact fields and I would like to
merge them into one database. The company I work for has two types of clients
that it keeps in two separate but identical (as far as the fields go)
databases and now they want me to put them all into one database. I realize
that having these two databases in the first place is not ideal but it is how
it is. Is it possible two synchronize these into one database or am I stuck
with two?

 

[KARL DEWEY]

Add a field that distinguishes the databases, update that field, and then
append the other.

 

[Jeff Boyce]

In addition to KARL's suggestion, do you need to resolve any potential
conflicts with primary keys?

That is, if each db keeps its own list, with primary keys, then both
together could have two clients with the same value for the primary key
field. Not a good idea!

And if your dbs have related tables that are "children" to the client lists,
the primary keys will need to be able to point to the correct "child"
records after the merger.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[bryan]

These databases are as basic as can be. There are fields only for client
name, address, phone number and the date they signed up with our company. One
database is for clients who fall under federal regulations for the D.O.T. and
the other is for clients who do not. I realize that all is needed is to add a
field that could be used to identify each as such but I inherited this and
don't really know that much about it (had a few classes in college). The
primary key for both tables is simply the company name. All I need to do is
to get both databases into one table without having to re-enter every client.
As there are about 3000 total clients you can imagine how long that would
take. For a number of reasons it no longer matters if they are in separate
databases and they want me to just put them all into one.

 

[John W. Vinson]

These databases are as basic as can be. There are fields only for client
name, address, phone number and the date they signed up with our company. One
database is for clients who fall under federal regulations for the D.O.T. and
the other is for clients who do not. I realize that all is needed is to add a
field that could be used to identify each as such but I inherited this and
don't really know that much about it (had a few classes in college). The
primary key for both tables is simply the company name. All I need to do is
to get both databases into one table without having to re-enter every client.
As there are about 3000 total clients you can imagine how long that would
take. For a number of reasons it no longer matters if they are in separate
databases and they want me to just put them all into one.

Just to clarify:

Are you talking about two different *databases* - mdb files containing
(potentially) many tables, reports, forms, queries?

Or are you talking about two different *tables*?

Or perhaps about two different databases, each with one table and nothing
else?

It's (relatively) easy to run an Append query to create one big table from two
existing tables, and it sounds like your app is straightforward enough to make
this pretty easy - but the details of the process will be different.

Note that a Company Name is a *bad* choice of primary key. A primary key must
be unique (only occuring once in the table); it should ideally also be stable
(not likely to change its value over the course of the use of the database)
and short (that's why 4-byte Long Integers or Autonumbers are commonly used).
Company names fail on all three counts.

 

[bryan]

Sorry I know I used the term "databases" in a generic sense when in fact they
are two tables not separate .mbd files. I used the company name as the
primary key because it will only occur once. The entire function of this
"database" is basically that of a big rolodex. Just basic contact
information.

 

[John W. Vinson]

Sorry I know I used the term "databases" in a generic sense when in fact they
are two tables not separate .mbd files. I used the company name as the
primary key because it will only occur once. The entire function of this
"database" is basically that of a big rolodex. Just basic contact
information.

In that case, I'd just create a third table (copy and paste one of the
existing ones, structure only no data). Add a new yes/no field [DOTRegulated].
In the Query include a calculated field

DOTRegulated: True

for the one table, and use False for the other. Run each query to populate the
new table, check everything out, and then delete the old tables (you *did*
make a backup copy of the database first... *RIGHT*?)

 

[bryan]

Ok I just noticed that in fact some of the same companies exist in both of
the tables. I wish I knew more about this (I guess it's a learning
experience) but it seems like that's not a very good design. How can I merge
both tables and not lose or have duplicates in the new table? And oh yes I
have saved this stuff in 2 different locations.

Sorry I know I used the term "databases" in a generic sense when in fact they
are two tables not separate .mbd files. I used the company name as the
primary key because it will only occur once. The entire function of this
"database" is basically that of a big rolodex. Just basic contact
information.

In that case, I'd just create a third table (copy and paste one of the
existing ones, structure only no data). Add a new yes/no field [DOTRegulated].
In the Query include a calculated field

DOTRegulated: True

for the one table, and use False for the other. Run each query to populate the
new table, check everything out, and then delete the old tables (you *did*
make a backup copy of the database first... *RIGHT*?)

 

[John W. Vinson]

Ok I just noticed that in fact some of the same companies exist in both of
the tables. I wish I knew more about this (I guess it's a learning
experience) but it seems like that's not a very good design. How can I merge
both tables and not lose or have duplicates in the new table? And oh yes I
have saved this stuff in 2 different locations.

Well... take your pick. Do you want to not lose data, or do you want to avoid
duplicates? If you have two records for a company you must choose one or the
other; you CAN'T have both. Please explain your desired result.

 

[bryan]

All I want is for all the records in both tables to transfer into one
complete table without having to re-enter all the records again. A
synchronization if you will...

 

[John W. Vinson]

All I want is for all the records in both tables to transfer into one
complete table without having to re-enter all the records again. A
synchronization if you will...

Again:

You have a record for AT&T in one table.
You have a record for AT&T in the other table, with *different data*.

What do you want in the final table?

You say you want all the records.
You also say you cannot have any duplicate company names.

These two requirements are in conflict. How do you want that conflict
resolved?

 

[bryan]

Ok. I have two tables. They both consist of these 5 fields: Company Name,
Address, State, Zip Code, Phone Number. Not much of a database but it serves
its purpose of having a quick lookup for company info without having to dig
through a huge rolodex. There are some companies in one table and some
companies in the other. I want all the records in both tables to be put into
one table without having to re-enter every record. The reason I say that I
don't want any "duplicates" is because some of the companies appear in both
tables but the records are exactly the same. I'm not sure how some companies
got entered into both tables but like I said I kinda inherited this mess.
People are getting confused about which table is which and that's why they
want me to put these two tables into one in the first place. Sorry for all
the confusion and thanks for all the patience.

 

[Michael Gramelspacher]

All I want is for all the records in both tables to transfer into one
complete table without having to re-enter all the records again. A
synchronization if you will...

This is the general concept. Table2 is merged into table 1, excepting the
duplicates.

INSERT INTO Table1
(field_a,
field_b,
field_c)
SELECT field_1,
field_2,
field_3
FROM Table2
WHERE NOT EXISTS (SELECT *
FROM Table1
WHERE Table1.field_a = Table2.field_1
AND Table1.field_b = Table2.field_2
AND Table1.field_c = Table2.field_3);

 

[John W. Vinson]

Ok. I have two tables. They both consist of these 5 fields: Company Name,
Address, State, Zip Code, Phone Number. Not much of a database but it serves
its purpose of having a quick lookup for company info without having to dig
through a huge rolodex. There are some companies in one table and some
companies in the other. I want all the records in both tables to be put into
one table without having to re-enter every record. The reason I say that I
don't want any "duplicates" is because some of the companies appear in both
tables but the records are exactly the same. I'm not sure how some companies
got entered into both tables but like I said I kinda inherited this mess.
People are getting confused about which table is which and that's why they
want me to put these two tables into one in the first place. Sorry for all
the confusion and thanks for all the patience.

If the company name is the Primary Key as you indicate upthread, you can
simply run an append query:

INSERT INTO Table1 ([Company Name],
[Address], [State], [Zip Code], [Phone Number])
SELECT [Company Name],
[Address], [State], [Zip Code], [Phone Number] FROM Table2;

Copy and paste this SQL into the SQL window of a new query; change Table1 and
Table2 to the actual names of your tables (as written it will add all of the
records from Table2 into Table1). When you run the query you'll get a warning
message

32 records were not added due to key violations

or something of the sort; these are just the duplicates that are being
rejected, everything else will be added.

 

[bryan]

Perfect! Thank you John and everyone else for all the help and patience. My
new mission is to learn as much as possible about working a database.

Ok. I have two tables. They both consist of these 5 fields: Company Name,
Address, State, Zip Code, Phone Number. Not much of a database but it serves
its purpose of having a quick lookup for company info without having to dig
through a huge rolodex. There are some companies in one table and some
companies in the other. I want all the records in both tables to be put into
one table without having to re-enter every record. The reason I say that I
don't want any "duplicates" is because some of the companies appear in both
tables but the records are exactly the same. I'm not sure how some companies
got entered into both tables but like I said I kinda inherited this mess.
People are getting confused about which table is which and that's why they
want me to put these two tables into one in the first place. Sorry for all
the confusion and thanks for all the patience.

If the company name is the Primary Key as you indicate upthread, you can
simply run an append query:

INSERT INTO Table1 ([Company Name],
[Address], [State], [Zip Code], [Phone Number])
SELECT [Company Name],
[Address], [State], [Zip Code], [Phone Number] FROM Table2;

Copy and paste this SQL into the SQL window of a new query; change Table1 and
Table2 to the actual names of your tables (as written it will add all of the
records from Table2 into Table1). When you run the query you'll get a warning
message

32 records were not added due to key violations

or something of the sort; these are just the duplicates that are being
rejected, everything else will be added.

 

[David W. Fenton]

Thank you John and everyone else for all the help and patience. My
new mission is to learn as much as possible about working a
database

John's suggestion is a pretty arbitrary way to resolve the
duplicates. How do you know that the second table does not have
useful data in it, such as phone numbers that are missing from the
first table?

 

[John W. Vinson]

John's suggestion is a pretty arbitrary way to resolve the
duplicates. How do you know that the second table does not have
useful data in it, such as phone numbers that are missing from the
first table?

True, but I was relying on the accuracy of Bryan's statement upthread:

The reason I say that I
don't want any "duplicates" is because some of the companies appear in both
tables but the records are exactly the same.

If there are in fact discrepancies you would certainly need a more complex
approach; the worst case would be that both tables have a phone number... but
they are DIFFERENT phone numbers.