Merging Two Identical Databases (Access 2000 format)

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?

Add a field that distinguishes the databases, update that field, and then
append the other.
--
KARL DEWEY
Build a little - Test a little


"bryan" wrote:

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?

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" wrote in message
news
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?

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.

"Jeff Boyce" wrote:

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" wrote in message
news
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?

On Thu, 12 Jun 2008 08:50:03 -0700, bryan
wrote:

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.

--

John W. Vinson [MVP]

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" wrote:

On Thu, 12 Jun 2008 08:50:03 -0700, bryan
wrote:

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.

--

John W. Vinson [MVP]

On Thu, 12 Jun 2008 10:40:03 -0700, bryan
wrote:

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 [MVP]

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.


"John W. Vinson" wrote:

On Thu, 12 Jun 2008 10:40:03 -0700, bryan
wrote:

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 [MVP]

On Thu, 12 Jun 2008 12:36:05 -0700, bryan
wrote:

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.
--

John W. Vinson [MVP]

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" wrote:

On Thu, 12 Jun 2008 12:36:05 -0700, bryan
wrote:

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.
--

John W. Vinson [MVP]