Primary Keys 1

[CathieF]

I have a dozen or so tables all linked by a primary key of a patientID and SiteID.
I have a table that requires more than one entry for the same patient. It won't let me add more data for that one patient. I am supposing this is becuase the primary key involves the patientID and cannot be duplicated? Can I still link all the tables and have all my sorts and functions work with this not the primary key( i.e. make an autonumber just count the entries as my primary key)? Is there a better way to allow for multiple entries of a patient?

 

[jebry]

Hi!

What you have is called a one-to-many relationship. This type of relationship occurrs often and is characterized by many records in one table being related to one record in another. An example would be the relationship between patients and office visits. You are exactly on the right track when it comes to handling this type of relationship. You take the primary key from the one side of the relationship and put it in the table on the many side of the relationship. Obviously, as you have discovered, this field cannot, by itself, be the primary key of the many table. Using an autonumber for the primary key is perfectly acceptable and you will still be able to use the patientID to connect records in the two tables.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[LarryDeLaruelle]

Cathie:

To add to what Jeff said, it sounds like you may not have normalized your data properly.

The patient table would contain only that information that is specific to the patient -- Identifier, locator, etc.

A separate table would contain information on Office Visits (or whatever). This table would have the PatientID (indexed, duplicates OK) as the linking field back to the patient table. A primary key would not necessarily have to set.

Does this help? Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[CathieF]

THanks for the info.
Just one more question. THe problem, then lies with the relationship I formed between the two table? It should be a 1-many? Is that:
"All the records from the Table with Basic Patient Info and only those in the Office Visits that match"
or
"All the records in the tablie with Office Visit and only those in Basic Patient Info that match"?
Right now I am using 1-1 (only those that have matches in both tables)
Larry: I do have a table that has information about office visit results and one about basic patient info (size, height weight, diagnosis) Are you saying the table about the office visits doesn't need a primary key?

 

[jebry]

Hi!

The relationship will be one to many no matter how the tables are joined. The issue you bring up here is how to join the tables(left join, right join, or inner join). The one to many just indicates that the second table can have multiple records related to the first table. An inner join requires related records in both tables before the query will select and the left and right joins will select all from one table or the other and provide related information from the other table where it is available. It sounds like you are doing fine with your Db, just make sure that patientID is not the PK for the table with multiple entries per patient. To answer your question to Larry, Access never requires a primary key in the table, but there is no harm in adding an autonumber field to serve as the primary key.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[LarryDeLaruelle]

Cathie:

Ditto what Jeff said. I perhaps misunderstood your original post. I think Jeff hit it on the head that the linking field in the many table can't be the primary.

Good luck. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[CathieF]

Jeff-
Thanks for the vote of confidence.

It still won't let me add another entry with the same patientid. I made sure the primary keys were correct. It says there is not a related record in the table with the basic information about the patient. In the relationship information, it says the relationship between the tables in question are one-to-one. How can I change this if I cannot add more records for the same patient?

Thanks!

 

[james33]

I agree with all Larry and Jeff say the only thing I would add is that the basic information about the patient contains, in your words:
basic patient info (size, height weight, diagnosis)
This means that under the system as it stands, you will only be able to have 1 Diagnosis per Patient without having to enter the Basic Customer Info again.
The Diagnosis should go in the same Table as the Visits Then when the customer visits are for another (Different) 'Condition' the diagnosis is able to be different too.
Hope this helps

 

[CathieF]

James-
I am aware that by putting the diagnoisis in that table only one diagnosis can be entered. THis is what I wanted to do. This data is about followup after surgery and rehabilitation. The patients have only one diagnosis. It is not like your family doctor's office where each visit brings a new diagnosis.