Two-Field Primary Key with one Null Value

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

Default to a dash or underscore to fill in something.
--
KARL DEWEY
Build a little - Test a little


" wrote:

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

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







wrote in message
...
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

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



On Aug 14, 1:44*pm, "Steve" wrote:
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

wrote in message

...



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- Hide quoted text -

- Show quoted text -

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


wrote in message
...
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



On Aug 14, 1:44 pm, "Steve" wrote:
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

wrote in message

...



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- Hide quoted text -

- Show quoted text -

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

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


wrote in message
...
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

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

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


wrote in message
...
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

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