Multiple entries in a table with one form? How

[SubjectiveEffect]

My database looks like this:

Patient data:
Patient number (primary key)
Patient DOB

Co-morbidity table:
Co-morbidity number (primary key)
Patient number (foreign key)
Co-morbidity type

Antibiotic table:
Antibiotic number (primary key)
Patient number (foreign key)
Antibiotic type

So each Patient can have multiple co-morbidities and multiple antibiotics. These are many-many relationships since each co-morbidity or antibiotic can be related to many patients. It needs extra tables in between Patient data and the other tables, so at the moment I have:

Antibiotic link table:
Antibiotic link number (primary key)
Patient number
Antibiotic number

Co-morbidity link table:
Co-morbidity link number (primary key)
Patient number
Antibiotic number

Ja. So why is this being posted in Access Forms?

Because I want to be able to do this (and I can't atm):
Add a new Patient, plus multiple co-morbidities and antibiotics for the same patient. I've tried copying the "Antibiotic type" and "Co-morbidity type" fields on the form but when I do opening the form produces a blank form - no fields. Without them I can create a form for just entering patient data.

Anyone any ideas?

 

[MajP]

First your tables do not look correct. These tables should not have any key to a patient

Co-morbidity table:
Co-morbidity number (primary key)
Co-morbidity type
other fields unique to a comorbidity

Antibiotic table:
Antibiotic number (primary key)
Antibiotic type
other fields about an antibiotic

Now my assumption is that there is two types of antibiotics, general antibiotics to treat the main disease and ones specific to a comorbidity. If all antibiotics are related to a specific comorbidity then disregard the discussion on general antibiotics. However if antibiotics are related to a comorbidity then you need 3 foriegn keys in this table:

Co-morbidity link table:
Co-morbidity link number (primary key not really needed)
Patient number (fk)
Antibiotic number (fk)
co-morbidity number (fk)


General Antibiotics for main disease:
So assuming that there are general antibiotics not linked to a specific comorbidity, then simply add a subform. This subform is based on a query that links your antibiotic link table to the antibiotic table. When you put this subform on your main form you link the subform to the main form by patient ID and hide this field. Now in the antibiotic number in the subform, you use a combobox. The combobox allows you to select an antibiotic (and shows the name), but it stores a antibiotic number in the link table.

Comorbidities and specific antibiotics:
Now assuming you have specific comorbidities with specific antibiotics. You put another subform on the main form. This subforms query links the comorbidity link table to both
the antibiotic table and the comorbidity table. Put this subform on the mainform and link it to the main form by patient number. Now on this subform you have two combo boxes, one form antibiotic number and one for comorbidity number. Again the trick is to show the name of the antibiotic and the name of the comorbidity but save the number for each.

The way to show a name but save the number is to have two fields in your combobox. The first field is the number the second is the name. Set the width of the first field to 0 in the field width property of the combobox.

 

[SubjectiveEffect]

Thanks, I'll give the sub-form method a try.

FYI - the antibiotics and co-morbidities are not related. Yes, it is true that some antibiotics are specific to some of the co-morbidities in some cases but it is not necessary to make any link because there are no hard and fast rules.

 

[MajP]

That makes it a little simpler. You have two "many to many" not a "many to many to many" (ie. patients to morbidities to antibiotics)

If patients have many comorbidities and patients have many antibiotics then your tables should be like.

Co-morbidity table:
Co-morbidity number (primary key)
Co-morbidity type
other fields unique to a comorbidity

Antibiotic table:
Antibiotic number (primary key)
Antibiotic type
other fields about an antibioti

Antibiotic link table:
Antibiotic link number (primary key not really needed)
Patient number
Antibiotic number

Co-morbidity link table:
Co-morbidity link number (primary key not really needed)
Patient number
comorbidity number
(no Antibiotic number in this table)

So now it is simply two subforms, one for patient antibiotics and one for patient comorbidities. Both subforms are linked by patient number.

Antibiotic subform:
The query for this subform is the antibiotic link table linked to the antibiotic table. You will have a combobox on the antibiotic number fk from the antibiotic link table.

Comorbidity subform:
The query for this subform is the comorbidity link table linked to the comorbidity table. You will have a combobox on the comorbidity number fk from the comorbidity link table.

In your link tables you have primary keys which I assume are auto numbers. You do not really need them, but you want to ensure that you have a combined index on the two foriegn keys.

For example you would have values like

Patient Antibiotic
Number Number
1 5
1 2
1 9
2 1

Patient 1 has antibiotics 5,2, and 9
Patient 2 has antibiotic 1

You do not want the user to put another combination of 2,1 which would give patient 2 another record for antibiotic 1.