Merging Two Identical Databases (Access 2000 format)

On Fri, 13 Jun 2008 07:01:01 -0700, bryan
wrote:

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

John W. Vinson [MVP]

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.


"John W. Vinson" wrote:

On Fri, 13 Jun 2008 07:01:01 -0700, bryan
wrote:

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

John W. Vinson [MVP]

On Fri, 13 Jun 2008 07:01:01 -0700, bryan
wrote:

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);

On Mon, 16 Jun 2008 10:29:01 -0700, bryan
wrote:

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

John W. Vinson [MVP]

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.


"John W. Vinson" wrote:

On Mon, 16 Jun 2008 10:29:01 -0700, bryan
wrote:

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

John W. Vinson [MVP]

=?Utf-8?B?YnJ5YW4=?= wrote in
:

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?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

On 18 Jun 2008 00:33:36 GMT, "David W. Fenton"
wrote:

=?Utf-8?B?YnJ5YW4=?= wrote in
:

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?

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

John W. Vinson [MVP]