Hello, I'm trying to set up a clinical trial form that will update records in multiple related tables in an Access DB. The basic DB schema is that there is:
1. A tblPatients table that contains a demographic record for each patient, indexed by a unique SubjectID
2. A tblEvents table that contains a record for each medical event that occurs to the patient, it has an autonumbered EventID index, and has a column for the SubjectID, EventTypeID
3. A tblAdmissionsDiagnosis table that should have a single record inserted IF the patient had a hospital admission event. In other words, a single patient may have multiple events, and if any of those events are a hospitalization event, there should be one record in the tblAdmissionsDiagnosis table. This table is indexed by the EventID index of the hospitalization.
The form is used to enter data regarding one Subject only. In other workds, the SubjectID should not be a repeating section, but the events ARE a repeating section. I've had some problems with this, but solved it by putting a hidden Master repeating table, where each row gets filled by the Subject ID entered into a text field.
But this system breaks down when I try to include the tblAdmissionsDiagnosis table. I either get no records added, or I get multiple records, one for each event, whether or not they were hospitalizations, or I get various errors from InfoPath when I try to add the table to the Master Data Source.
Given the database schema I'm trying to implement, can anyone suggest how to structure the Data Sources, or how to restructure the database schema to better lend itself to Infopath forms?
Regards,
Sean