Two-Field Primary Key with one Null Value

[rjm972]

Hi all,

I'm new to databases and have a question that's hard for me but may be
easy for someone else.

I have a database where patients are assigned two unique IDs (hospital-
wide and clinic-specific). I would like to create a primary key based
on both of these values. I know how to do this - but here's where I'm
stumped.

I need to ensure that *at least one* of those ID values is entered for
each patient. If one ID is entered, the other can remain blank. And
vice-versa. Entering both ID values is fine also.

Right now, the ID values and other info (name, birth date, etc.) are
entered into a form. Is there any way to set these two unique ID
values as the primary key, and allow one or the other to remain empty
(null)?

Thanks,
Rob

 

[KARL DEWEY]

Default to a dash or underscore to fill in something.

 

[Steve]

I suggest you relook at the structure of your tables. You should have tables
that look something like:

TblHospital
HospitalID
HospitalName
<other hospital specific fields>

TblClinic
ClinicID
ClinicName
<other clinic specific fields>

TblHospitalClinic
HospitalClinicID
HospitalID
ClinicID

TblPatient
PatientID
FirstName
LastName
<other patient specific fields>

TblPatientHospitalClinic
PatientHospitalClinicID
HospitalClinicID
PatientID

I have assumed there are multiple hospitals and each hospital has multiple
clinics. TblPatientHospitalClinic records which hospital and which clinic at
that hospital a patient is treated. You do not need a dual primary key.

Steve

 

[rjm972]

Thanks for the replies.

Actually there is only one hospital and clinic of interest. I need to
track patients in one clinic only, and need to know either their
hospital ID, or their clinic ID, or both. This is to prevent
duplicate patient entries, i.e., where the same patient gets entered
several times because of multiple referrals.

Based on that, would it make sense to have only one table? And is
there any way to uniquely identify the patients to prevent duplicate
entries?

Thanks again!
Rob

 

[Steve]

Please explain ..............
For a given medical condition, a patient is admitted to a hospital/clinic.
Are you saying that for that medical condition, the patient might be being
treated by more than one doctor and more than one doctor refers him to the
hospital/clinic? If so, which one do you want to be the referree?

Over time a patient needs to be admitted to the hospital/clinic more than
once. It might be for the same condition or for different conditions. To
record this would require entering the same patient multiple times in the
admissions table.

Also, since there is only one hospital and one clinic, it is not necessary
to record the hospital (HospitalID) and Clinic (ClinicID) in the database
since all records in the database apply to the same hospital and clinic.

Steve


Thanks for the replies.

Actually there is only one hospital and clinic of interest. I need to
track patients in one clinic only, and need to know either their
hospital ID, or their clinic ID, or both. This is to prevent
duplicate patient entries, i.e., where the same patient gets entered
several times because of multiple referrals.

Based on that, would it make sense to have only one table? And is
there any way to uniquely identify the patients to prevent duplicate
entries?

Thanks again!
Rob

 

[rjm972]

Thanks Steve.

What I'm trying to do is create a simple waitlist for one clinic in a
large hospital. Patients have a hospital ID, which everyone uses.
They also have another ID which our clinic uses. Sometimes the
patient gets entered into the database several times because staff
don't realize they're already in the database. Therefore, I'm trying
to find a way to prevent duplicate patient entries. The only way I
know of is to uniquely identify them. The complication is that
sometimes either the hospital ID or the clinic ID are not available.
At least one ID will be available, but often one or the other is
missing. Still, I need a way to "force" staff to enter at least one
ID so that patients don't get entered several times unnecessarily.

Thanks,
Rob

 

[Steve]

Rob,

There's still the question about what to do if a patient needs to be
admitted multiple times over time (say this month and then again in
October).

Steve

 

[rjm972]

Hi Steve...not an issue. As long as they're already in the database /
on the waitlist, multiple admissions don't need to be tracked for our
purposes.

Thanks,
Rob

 

[Steve]

Rob,

Can the tables in your database be changed?

HospitalID and ClinicID can not be used as a dual primary key since a
primary key requires entering both values. If your staff are entering
duplicate patient records, they have to be starting with a new record to
create a duplicate. There needs to be a way to first have to enter
HospitalID and/or ClinicID before other patient data (name, etc) can be
entered and then check to see if one or the other has been entered
previously. I have an idea on how to do it but it requires a change in your
tables and data entry form.

Steve

 

[rjm972]

Thanks Steve. I understand your point; good idea. I'll play around
with it this weekend. Thanks so much for all of your help!

Cheers,
Rob

 

[Steve]

Rob,

TblPatient
PatientID
HospitalID
ClinicID
FName
LName
Address
City
State
Zipcode
<Other patient related fields>

For HospitalID and ClinicID, set the Index property to Yes(No Duplicates).
(Do this in the design view of the table.)

Create a form named FrmPatient based in TblPatient and onlly include the
fields:
PatientID
HospitalID
ClinicID

Create a form named SFrmPatient based in TblPatient and onlly include the
fields:
PatientID
FName
LName
Address
City
State
Zipcode
<Other patient related fields>

Open SFrmPatient in design view. Open Properties. Go to the Format tab and
turn off the navigation buttons. Go to the Other tab and change the Cycle
property to Current Record. These two things will make it impossible to go
to more than one record for the same Patient in FrmPatient after you make
SFrmPatient a subform on FrmPatient.

Open FrmPatient in design view. Click on Windows - Tile Vertically and click
and drag SFrmPatient and drop it on FrmPatient to make it a subform on
FrmPatient. Select the subform control and open Properties. Go to the Data
tab and be sure the LinkMaster and LinkChild properties say PatientID.

Open FrmPatient in design view. Select the subform control. Open properties
and go to the Events tab. Enter some code in the Enter event that keeps you
from enterng the subform unless either HospitalID or ClinicID is not null.

Good Luck!

Steve
(e-mail address removed)

 

[Guest]

wide and clinic-specific). I would like to create a primary key

on both of these values. I know how to do this - but here's

No you don't. A primary key based on both those values would
allow

A 1
A 2
B 1
B 2

-- all different, 4 entries, but only 2 clinic ID's and 2 Hospital
ID's and only one patient. Or, assuming that your hospital is
better than your clinic, and never creates duplicate ID's (unlike
every other hospital in existence :~)

A 1
A 2

What you want is a unique index on Hospital ID, and a unique
index on Clinic ID.

A -
or
A 1
or
A 2
or
- 1


Then, you also want a primary key if you have another table to
join to. The primary key doesn't have to be anything - it can be
just an autonumber. Primary keys are used to make updateable
queries in Access. If you have Hospital ID's and duplicate client ID's
it's probably best not to use those ID's as database ID's inside
your database.

(david)