problem with merging data

[Guest]

I got stuck rather quickly on this one: I recently
programmed a hospital database with all the patient data
in one table linked to a table with all the hospital
admissions in another, the linking key being their HKID
number.
For each entry in the admission table, I have several
tables linked on the HKID and on an autonumber, because
these are unique to each admission, which I (naturally)
kept hidden from the user.
So far so good.


The problem comes in that now the database is to be
expanded to two other hospitals, and every so often, the
two other hospitals are to send their data to the one I'm
working for, and the data is to be collected there for
research. These hospitals do not have a common server
that I can use as a back-end, and the autonumber will
totally screw records up when I want to merge records. So
what is my best bet? - aside from making another field in
the admission table part of the primary key - I'd hate to
do that.

Thanks to anyone who replies. I hope I phrased my
question clearly enough.

Chris

 

[Tim Ferguson]

two other hospitals are to send their data to the one I'm
working for, and the data is to be collected there for
research. These hospitals do not have a common server
that I can use as a back-end, and the autonumber will
totally screw records up when I want to merge records. So
what is my best bet? - aside from making another field in
the admission table part of the primary key - I'd hate to
do that.

It's exactly what I was about to suggest -- why would you hate it?
Presumably you'd need a field to document which hospital the admission
happened into anyway, so adding it to the PK does not seem much of a
burden.


HTH


Tim F

 

[Guest]

It's exactly what I was about to suggest -- why would you hate it?
Presumably you'd need a field to document which hospital the admission
happened into anyway, so adding it to the PK does not seem much of a
burden.


HTH


Tim F

Wait, I'm not quite sure what you mean.

Case 1: Using a HKID/Hospital Name as a PK
The problem behind this being: a patient may visit the same
hospital twice, so a HKID (permanent throughout one's
lifetime)/hospital name PK does not uniquely identify an
admission. I was going to link it to the admission time,
but in about 5 out of 100 cases, the doctors don't know the
admission date, and, for statistical purposes, don't want
to enter a default, impossible date (I'd personally agree).
Nor does there seem to be a value that uniquely identifies
each admission that is necessarily known to the users when
they enter data.

Case 2: Using a HKID/Hospital Name/Autonumber as a PK
Autonumber will still screw up my relationships when I
append the table (or is there some way around this?).

Therefore I seem to be stuck with creating something that
uniquely identifies each admission (currently the
HKID/Autonumber PK), and nothing better had occurred to me
than the autonumber.

Basically, if there is a good way me to uniquely identify
admissions across all three databases, without requiring
specific admission data, that'd be the best thing in the world.

That's my situation. I'm desperately trying to not to have
to write a function that will merge the data through the
complicated process. If I didn't get what you meant, Tim,
please help me

TIA

Chris

 

[Lynn Trapp]

Answers below inline:

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Wait, I'm not quite sure what you mean.

Case 1: Using a HKID/Hospital Name as a PK
The problem behind this being: a patient may visit the same
hospital twice, so a HKID (permanent throughout one's
lifetime)/hospital name PK does not uniquely identify an
admission. I was going to link it to the admission time,
but in about 5 out of 100 cases, the doctors don't know the
admission date, and, for statistical purposes, don't want
to enter a default, impossible date (I'd personally agree).
Nor does there seem to be a value that uniquely identifies
each admission that is necessarily known to the users when
they enter data.

Please go back to square one and post your table structures. I have a
sneaking suspicion that there is something not quite right about it, but
can't be sure without knowing what your tables look like.

Case 2: Using a HKID/Hospital Name/Autonumber as a PK
Autonumber will still screw up my relationships when I
append the table (or is there some way around this?).

Therefore I seem to be stuck with creating something that
uniquely identifies each admission (currently the
HKID/Autonumber PK), and nothing better had occurred to me
than the autonumber.

An Autonumber field does NOT guarantee unique records. If an Autonumber
field is the only member of the PK, then you can actually enter 2 identical
records that differ only by the value in the autonumber field. That is
hardly uniqueness. Autonumbers are good for linking fields, but not so good
as unique identifiers.

Basically, if there is a good way me to uniquely identify
admissions across all three databases, without requiring
specific admission data, that'd be the best thing in the world.

That's my situation. I'm desperately trying to not to have
to write a function that will merge the data through the
complicated process. If I didn't get what you meant, Tim,
please help me

The only reliable way to do what you want is to use Access Replication.
There is a white paper available on how to set up replication and a lot of
information at www.trigeminal.com.

 

[Guest]

My table structure is as follows:
N.B.: The HKID is government assigned and stays the same
throughout your lifetime


general patient data (PK = HKID)
--------------------------------
HKID Last Name First Name ....


linked one-to-many to (by HKID)


admissions (PK = HKID/Autonumber)
---------------------------------
HKID Admission ID (this is the autonumber)

Admission date Discharge date ......


linked one-to-many to numerous other tables by HKID and
Admission ID

Thanks for the heads up on replication. All of my Access
knowledge came from learning by doing, so I don't know
anything about replication whatsoever, but I'll take a look
at the paper.

Thanks for all your help so far


Chris

 

[Lynn Trapp]

Ok so far. I wondering if using HKID and Admission Date as a composite
primary key in the Admissions table wouldn't work. Since it's highly
unlikely that any one patient with a unique HKID would be admitted to a
hospital more than once at exactly the same date and time, then you would
have a fairly workable primary key.

 

[Guest]

I did realize your point, but the records are entered by a
technical assistant several months later and that technical
assistant may or may not have the admission date (yeah, no
clue why either) and may consequently leave it blank. For
sake of clarity, I would not like the user to have to enter
a default value that signals an unknown date.

I think I might have found a way around it though: I wrote
my own autonumber function that returns the highest number
in the admission table not yet in any record by that HKID.
Code is below

That generates a second value for the primary key unique to
each group of records with the same HKID, and as a bonus,
the autonumber won't screw things over when I'm uploading
data. Of course in the database I want to upload the data
from, I still have to check for duplicates, but with
referential integrity I can at least get the data into a
form that could just be copied and pasted over.

Thanks for your help though. I appreciate all the time you
put into this thread, and at least I don't feel like a
dunce anymore.

Chris

 

[Tim Ferguson]

That generates a second value for the primary key unique to
each group of records with the same HKID,

This would seem to guarantee a unique ID within each hospital, so you get
(Eric,1), (Eric,2), (Eric, 3) and so on. I don't see what happens when Eric
is admitted to another hospital and how you avoid creating (Eric, 1) again.
Can each hospital database "see" the data in other hospitals in real time?

A three-field key such as

(Eric, HospA, 1), (Eric, HospB, 1), (Eric, HospA, 2)

would seem to fit the need, since each database is generating unique IDs
within its own domain.

But if what you have works to your satisfaction, that's great!

All the best


Tim F