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.

 

[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

 

[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

 

[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

 

[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

 

[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)