Complicated subform?

[KateB]

I'm not sure I can even explain this simply enough to get a response but I'll
have a go!

I receive laboratory reports on a daily basis. Part of this report is a
list of antibiotics (approx 22 at the moment but this may grow!) to which the
sample is either sensitive or resistant. I understand that I need to store
this information in a separate table to the patient details, and can link the
2 tables by using the unique ID in the first table that will then relate to
the second table on a one to many basis. What I need (I think!) is a subform
that will list the antibiotics and the option of choosing either resistant,
sensitive or not tested for each sample.

I have a table that lists the antibiotics but don't know how to pull them
across to the subform so that the user doesn't have to keep adding records in
order to record the data - I would rather they were all displayed so that the
user can run down the list and choose the relevant response.

I hope this makes some sort of sense. I'm a newbie at database design etc
so if anyone can help please put it in simple terms!! Many thanks

 

[Douglas J. Steele]

It sounds as though you really need 3 tables: one for the patients, one for
the antibiotics, and one that represents the resolution of the many-to-many
between patients and antibiotics. (the primary key of this third table would
be the combination of the patient id and the antibiotic id).

This is very similar to the situation between Products and Orders in the
Northwinds database that comes with Access: each Product can appear on one
or more Orders, and each Order can contain one or more Products. To handle
that many-to-many, the Order Details table was introduced.

Take a look at the Orders and Orders Subform forms in that database as well.
The Orders form is bound to a query based on the Orders table, while the
Orders Subform is bound to a query based on the Order Details table. So that
it's not necessary to type in product names, the Orders Subform includes a
combo box whose RowSource is the Products table.