Avoiding Duplication

[YHennebery]

I am trying to create a database as follows:
Table 1 = Nursing Stations (Contains 1 field = Station Name)

Table 2 = Patient Info (Includes field Nursing Station, and room number, doctor, med counts, and date reviewed) There is a need to allow duplicate room numbers within a Nursing Station, but one of the records should have a field checked that the patient is not available.)

Table 3 = Recommendations for Patient (consisting of recommendation detail, type, class, and or pending status)

The number or recommendations per patient can vary, and shouldn't have a maximum.

I have gotten completely confused about the relationships, and all I want is to display a Master Form with the info from Table 1 & 2, with a detail section showing data from Table 3.

Any advice, opinions, or pointing in the right direction would be appreciated.

Thanks,
Yamilet

 

[annsolomon]

I would probably change the NursingStation in Table2 to a lookup field that references the NursingStation field in Table1.

Presumably, you'd allow a patient to return for more than one visit so I'd have a separate table (tblPatientInfo) with PatientID, PatientName, PatientAddress, etc.) I'd create another lookup field in Table2 that references the PatientID field in tblPatientInfo. I'd add a new field (VisitID) to Table2 and make that the primary key.

Then, I'd relate Table2 to Table3 on the VisitID field (one-to-many). Of course, this implies that you'd have to add a new field (VisitID) to Table3.

There are probably other ways to attack this, but this would be the way I'd handle it.

Ann