Multiple forms associated with one date

[HGCanada]

I am working on a database project (not related to the previous one), to store data on repeatedly completed forms for patients. Each patient comes for multiple visits. Each visit, they complete 2 separate forms. I have set it up in the following way:

- tPatientID - stores patient ID info
- PtIDAutoNumber (PK)
- PtName and other identifiers

- tPatientVisitDates - stores visit dates - multiple dates per patient, set up as a subform on the main patientID form
- PtIDAutoNumber (FK)
- VisitDateID (PK)
- VisitDate
- checkboxes for which forms were completed

- tPreparedness and tUnpacking - stores data from the 2 forms
- VisitDateID (PK, FK) - is it OK to have the same PK in these tables? It is a 1:1 relationship - each form is completed only once per patient per visit date.
- Preparedness/UnpackingIDAutoNumber (not sure this is needed, since only 1 form is completed per pt per day - not linked to anything)
- basic form questions

How can I set it up, such that I can:
- go the patient form
- enter multiple visit dates into the subform (this seems to be working OK)
- link with command buttons to the Preparedness or Unpacking forms for that patient, and enter the data, ideally with visit date already pre-populated.

Thanks.

 

[MarkK]

I would not create a 1:1 relationship between tables, just put all the data in one table.

Another approach you could take, depending on your purpose, is just store the patientID, the date and the data. There is no need to store the visit as a separate thing, since if there is data on that day, then the visit is assumed. Also, you may not need textboxes as meta-data to which forms were completed. You can calculate that result by calculating which forms were actually completed, which saves you entering that data.

Hope this helps,