Software

Ask something
Discussion Locked

Combining two databases in 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 mtnsqs

You have a big task ahead of you.

The best way to do this is to merge the two Db's into a NEW THIRD Db. You are going to have to write some extensive code.

The name matching will not work perfectly as there are bound to be errors in the way the names are typed into the two systems. In some cases there will be typeing errors effectively giving misspelling, or abbreviated first names will be used in one and not the other DB. The accuracy of your final Db will depend on the situations youallow for.

I suggest you start by putting a numerical key on both DB's as a RECORD ID. Then write some reports that you can use to "match" ids from one Db to the other. Write the pair of matching ids to a file.
You will then have to look at all the other entrys and relax the mathcing criteria to find possible matches, manually determine if they are Ok, and if ok add the pair of ids to the file.

Then write code to take the pairs of ids from the file and copy to the new Db, the fields from each of the original db's for those ids into one record.

Its not difficult, but tedious depending on how far you want to go with mathcing.

I've done a lot of this for patients records from multiple differnet db sources. We often found matching on Date of Birth for same or similar surname was a good way to find matches. We had a lot of Ethnic (mostly asain and indian origin) poeple in our data and they have a tendency to put surnames and first names in different order depending on the mood they are in on the day - so we had to also look for surname in one Db mathcing a fisrt name in other db!

by sankulpune

Hello Bommal,

You have two choices to add records from table2 to Table1.
A) Run a query
B) Run VBA code

Here is the implementation of choice A:

Assumptions:

Table1: with sample data

PKey (Primary Key): P1, P2, P3
fName: F1, F2, F3
sName: S1, S2, S3

Table2: with sample data

PKey: P1, P3, P1, P4, P2, P1, P4, P5, P1, P4, P5, P2, P2, P4, P3, P5
fName: F1, F3, F1, F4, F2, F1, F4, F5, F1, F4, F5, F2, F2, F4, F3, F5
sName: S1, S3, S1, S4, S2, S1, S4, S5, S1, S4, S5, S2, S2, S4, S3, S5

The solution:

Using following sql statements, create 4 queries in design view.

1) QueryA:
SELECT UCase(Trim([fName])) & UCase(Trim([sName]))As Name
From Table1;

2) QueryB:
SELECT UCase(Trim([fName])) & UCase(Trim([sName]))As Name, Table2.pKey, Table2.fName, Table2.sName
FROM Table2;

3) QueryBWithoutMatchingQueryA:
SELECT DISTINCT QueryB.pKey, QueryB.fName, QueryB.sName
FROM QueryB LEFT JOIN QueryA ON queryB.Name = queryA.Name
WHERE (((QueryA.Name) Is Null));
4) QueryAppend:
INSERT INTO Table1 (pKey, fName, sname)
SELECT [QueryBWithoutMatchingQueryA].pKey, [QueryBWithoutMatchingQueryA].fName, [QueryBWithoutMatchingQueryA].sName
FROM [QueryBWithoutMatchingQueryA];

After you successfully create these four queries (needs to be done only once), in future you need to run only the QueryAppend to append records from Table2 to Table1.

I have successfully tried this solution and would be glad to provide any help you may need to implement it.

To overcome the overhead of permanently maintaining the queries, you may use VBA code.

I would also be glad to provide the necessary code, if you feel interested.

With best compliments,

SanganakSakha,
Simple And Effective Solutions To Small Problems, No Quickfixes.

This discussion is closed to new replies.