How to set up a relationship for multiple test ordering on items

[Vetgeorge]

I'm having trouble setting up the relationships in my database or trying to implement the use of a subform for test ordering. What I'm trying to acomplish: Build a database that my doctors can use to order testing on tissue specimens.
I have doctors that microscopically examine surgical tissue samples. Each sample has a unique Accession number, that they will enter in the db. Each sample can have multiple parts (example: a lung specimen could have Part A, Part B, etc). Furthermore, each part could subsequently have multiple specialized tests ordered on them (example: Part A requires Test 1, Test 6; Part B requires Test 2, Test 6, etc)

So far I have built the following tables: One for Doctors name, one for the parts (A-L), one for the Tests, and a main table that includes fields to enter the specimen Accession number, the date, a drop down list to select the doctor, field to select the Part, and the field to select the test.

What I can't figure out is how to have additional entries for adding more parts and the ability to order more tests on those subsequent parts and still tied to the orginal accession number.
Any advice? Thanks


I also uploaded a picture of what I want my db to do

[June7]

Use 'junction' tables to avoid repetitive data entry that would be done in one big 'main' table. Consider:

tblDoctors
ID (PK)

tblTests
ID (PK)

tblSampleOrder
ID (PK)
DocID (FK)
TissueType

tblSampleParts
ID (PK)
SampleOrderID (FK)
Part (A-L)

tblPartTests
SamplePartID (FK)
TestID (FK)

Use form/subform/subsubform arrangement for data entry.

[Vetgeorge]

Thank you for your help about creating the junction table