Primary Key

[Guest]

As a medical biller, each patient is assigned a medical record number unique
to that patient. However one patient may have many procedures. I would like
to assign the medical record number as the primary key but cannot do so due
to primary key constraints of duplication. Should I just allow access to set
the primary key in this case or run some kind of query make table. i am
unsure.

 

[Guest]

Hi

Assuming that each patient can have more than one procedure - this is just
what access is best at.

Each patient has a number and assume that you have a table with these
details - name, address, date of birth, etc, etc.

You should create another table to hold details of medical procedures and in
this table you should include a field (of the same data type) to hold the
petient ID.

You can then link (one to many) the 2 tables (use the relationship window).
If you create a form for partients you can have a linked subform that will
display all procedures for that particula patient, etc, etc.

 

[Joseph Meehan]

As a medical biller, each patient is assigned a medical record number
unique to that patient. However one patient may have many
procedures. I would like to assign the medical record number as the
primary key but cannot do so due to primary key constraints of
duplication. Should I just allow access to set the primary key in
this case or run some kind of query make table. i am unsure.

First it should be the primary key of the "Procedure" The "Patient"
Table would be linked with a one (Patient) to many (Procedure)
relationship - assuming any one procedure would only apply to one patient,
think maternity here.

This is the way a relation database like Access works.

Second, remember that unless you have a need for it, like sorting data,
preventing duplicates or joining tables, there is no reason to have a
"Primary Key" field.