Enter data in one field of subform based on combo box selection(or checkbox?)

[sarajane_d]

Hello,
I have a database with patient information in it. Each patient may have
several visits, has several tests, each test comes from several test
kits, and each test kit has many tests. Currently I have a form set up
with tab pages for each type of patient information. On the Tests tab
page, I want to be able to select a test kit, and have all the test
names associated with that test kit to appear as individual records on
a subform, where I can enter the test details (like result, time...). I
would like the subform field "TestName" to be populated with the test
names from multiple tests. (So I can select Kit A, Kit B and Kit C, and
have all the tests associated appear. I was trying to do this with an
append query, but it would not repopulate the test names in additional
patient records. Can someone please help?
My tables are as follows:
tblPatientID: pkPatientID
tbl Visit: pkVisitID; patientID; KitID
tbl KitTestJxn: pkKitID; TestID
tbl Test: pkTestID;ResultID
tblResultkResultID
I may need some help with the relationships too.

 

[Ron2005]

I am wondering if perhaps there may be some confusion with the use of
the word "test".

Is it perhaps like this?
Patient John Doe
Visit 1/1/6
Test Catagory (Screening Catagory) Cholesterol
Test Kit 1234
TestKit Test1 HDL
TestKit Test2 LDL
Test Kit 1256
testkit test 1 total cholesterol
Test Catagory TSH
Test Kit 1222
testkit test PSA1

If that is the general scheme, then the results can simply be part of
that last table - no need for yet another table for results, but do
need the catagory table.

Tables for collected datacould be
tplPatient: pkPatient ID
tblVisit VisitID, Patient ID
tblCatagory CatID Visit ID CatDescription ID
tblKit kitID CatID KitdescriptionID
tbltest TestID KitID TestdescriptionID result

In each of those you can add logic to add all of the other keys back up
but you have to do the logic for it yourself, access will not.

supporting tables
tblCatDesc
CatDescriptionID Cat Description

tblKitDesc
KitDescriptionID Kit Description CatDescriptionID (Catagories that this
Kit can be used with)

tblTestDescription
TestDescriptionID TestDescription KitDescriotionID (Kits that this Test
can be used with)

This all depends on how you want to handle filling the tables and
maintaining results.