Linking Databases

[Martina]

I have 5 access databases which are all from different hospitals with
patient information like First Name, Last Name, DOB PatientNo etc, all of
them are set up in different formats by the hospitals.

I need to set up another database which is an index of all of the patients
in NSW and will need to be filled with a list of every person from all of
these other five databases and will be used to give them one unique ID no
which is consistent for every patient in NSW.

How do I create this Patien Index database and get it to the info from these
other databases?

I need it to be linked back to them because I need to be able to run a query
on the Patient Index database on a name and it will bring back the patient
records from all of the other databases.

 

[Jeff Boyce]

Martina

If I understand correctly, you are asking a database structure question, not
a "wiring" question.

Each hospital has it's own PatientID/No, right?

Your "combined" database could have it's own unique identifier (an
Autonumber seems reasonable), and a field for HospitalID (which one did this
record come from?) and that Hospital's PatientID/No (what number are THEY
using?).

Now the hard part -- how do you decide that "Smith, John" at HospitalA is
the same human being as represented by "J.J. Smith" at HospitalB? Until you
have that figured out (or a functional, effective universal personID), you
will have to expect that you will NOT get 100% of the matches, and that you
will potentially have some "false" matches.

Or have I missed your point?

 

[Martina]

No you have my point exactly, and this is only one part of what i have to
do, i have to sit a web service and web page on top of this, but with that
stuff aside,

So I set my Patient Index Database for example up with 4 columns -
PatientID, FirstName, LastName, Hospital/GP ID.

So when all or the people from each of these seperate GP/Hospitals are
imported(obviously some people will be in more than one so I need some way
of dealing with this, but I can work that out later) they are assigned a
Patient ID and their first name, last name and ID from whatever database
they came from are imported?

Does this sound right?

 

[Mike Painter]

No you have my point exactly, and this is only one part of what i
have to do, i have to sit a web service and web page on top of this,
but with that stuff aside,

So I set my Patient Index Database for example up with 4 columns -
PatientID, FirstName, LastName, Hospital/GP ID.

Add the DOB. First initial and first four letters of the last name along
with the DOB give a *very* good index. Twins tend to be the most common
problem. If you use firstname&lastname&DOB your problems will be reduced
quite a bit.

 

[Martina]

Thank you both for your help so far.

Now that I have set up my Central Patient Index with all of those columns,
how do I import the paitents from all of the other databases into this one?

 

[Jeff Boyce]

Martina

From inside the "new" one, link to the tables in each of the old ones.
Create an append query that takes rows from one of the old ones and appends
to the new one. Repeat with each old (linked) table until done.