Web Development

Ask something
Discussion Locked

Combining two databases in MS Access 97

0 helpful
5 replies

by bommal

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi,
I have a problem and it is very very important for me to come over it asap. I would sincerely appreciate if you can help me technically.

I have two databases in MS Access 97. One has a primary key which is a number and the other database does not have any primary key.
Both the databases have information about
a huge number of people.

The task I have in front me is to combine these two databases together and obviously to provide a primary key.

The problem is that there are some people who are common in both the databases, so I can't simply merge(the two tables that hold person information) them together as it may result in a same person can hold more than one record. I do not how I can solve this.

Also please let me knowwhether I can implement the following idea:
Can I pull the first name and last name of each person from a table in database2 and compare it with first name and last name of each record in a table in the first database1(which has a primar key for itself) and then assign a new number(for primary key )if no record matches, else I assign the same primary key incase it matches. If you think this works please tell me how I can implement this.

I hope you understand my problem. My email id is bommal@onebox.com and I would be more than happy to provide any further information.
Thanks in advance,
Warm regards,
Bommal.

5 replies

by peter ritchie

There's a couple of ways to approach this problem. One is to simply create a new database\table that contains the other two databases\tables combined. The draw-back of this is you probably will lose any primary keys--which is a bad thing. If whatyou're saying is true--and you have one table with primary keys and one without--the best bet is to move the records from the other table to the table with primary keys.

I'm assuming the table with the primary keys are auto-generated (autonumber in MS Access)

Now, with that in mind you need to create a query that returns the records in the second table (the non-keyed table) that AREN'T in the first table (the keyed table). Without knowing much about your schema I can only make some assumptions.

Lets say that the field sPhoneNumber is shared by each table and is considered unique to each person in the table. Assuming the table names are tKeyedPeople and tNonKeyedPeople, then a query that grabs only the people that don't exist in tKeyedPeople from tNonKeyedPeople might look like this:

SELECT tNonKeyedPeople.sFirstName, tNonKeyedPeople.sLastName, tNonKeyedPeople.sPhoneNumber FROM tNonKeyedPeople where tNonKeyedPeople.sPhoneNumber NOT IN (SELECT tNonKeyedPeople.sPhoneNumberFROM tKeyedPeople tKeyedPeople, tNonKeyedPeople tNonKeyedPeople
WHERE tKeyedPeople.sPhoneNumber = tNonKeyedPeople.sPhoneNumber)

This type of query is called a subquery. Basically, the embedded select queries for all phone numbers that are shared between the two tables. The main select searches for all phone numbers that are not in the embedded query (or numbers that aren't shared).

The next step is to insert those new records into your keyed table (AFTER backing it up, of course) with a INSERT INTO query. If you wanted to create an entirely new table you could use SELECT INTO.

by dennisbv

If all you want to do is combine the tables without importing duplicate names. Then import the table without the primary key, then go into dsign mode on the table with the primary key and select all the fields and set the all as primary keys. The save the table and create an append query based on the table without the primary key. Access will not import duplicate values into a primary key. When you run the query you will recieve a message stating that all the records could not be imported.This is the method I used to combine two tables with duplicate values into one table.
Dennis

This discussion is closed to new replies.