HELP!! Trouble with table structuring.

[Chantel]

I am constructing a health care database using access 2000. I have a table
for Patient Info., Patient tests., Patient reports.,..however; I have no idea
how to design a clinical history table. The problem I am having is that the
clinical history can have more than one value to one patient. For instance,
patient named "Leroy" can have a clinical history of "High blood pressure,
Cancer, and Diabetes". How can I structure this table and how can it then be
displayed on a data entry form?

I named the table "Clinical History"; there are two fields - clinical
history ID (Autonumber) and clinical history (Text). I have listed Cancer,
Diabetes, etc. in the clinical history field. In my Patient Info table I
have a field named "clinical history ID (Number) and in the lookup tab I have
in the Row Source "SELECT DISTINCTROW [Clinical History].*FROM [Clinical
History] ORDER BY [Clinical History].[clinical history];...........This would
works great if one value is selected, however when multiple values apply to
one patient what do I do?

 

[mnature]

You need to link your patient info table with your clinical history table.
You do that through a third table. This third table will have its own
primary key, and include the names of the primary keys from patient info and
clinical history tables. For each distinct primary key of the third table,
you will have a patient id and a clinical history id. You can have many
clinical histories tied to a single patient, and many patients tied to a
clinical history.

Set up your main form to select a patient, and have a subform from which you
can select the clinical histories, probably in datasheet view so you can see
multiple histories at the same time. Link the subform to the main form by
using the patient id.