Trying to set up a relationship (1 Viewer)

[CuriousGeo]

Greetings everyone.
I'm trying to set up a database to order and track tests that need to be run on surgical tissue samples. They currently do this with paper request forms, and I wanted to automate the task for the doctors I work for.
Basically a tissue sample is identified by an accession number (12-15008)
Each tissue can have multiple Parts (A, B, C, etc)

Each Part can have multiple tests requested (Part A: needs to have Test1 and Test2; Part B needs to have Test5 and Test 8; etc.)

First, I need to set up a junction table from what I've been reading on my own. But I just can't understand how go about setting up this many to many (am I right?) relationship.

Second, I have to make an ordering form that will have to have a subform in it which shows the parts and tests that can be selected.

Is my thinking correct in what needs to be done or is there a better way?
I can upload the basic stage of my database if it will help, I have made tables consisting of the doctors, parts (A-L), and tests available.

Thanks everyone

My attachment is what I eventually want the ordering form to look like

 

[plog]

I think your form will actually be multiple forms. But that's a discussion to have after you get your tables structured properly. From what you've described you should have 3 primary tables: Tissues, Parts, Tests.

You will then work in that order entering data. First you enter Tissue data into the Tissues table, every tissue will get its own autonumber primary key (TissueID). Next, you will enter every part of a tissue into the Parts table along with the TissueID of the tissue it belongs to. This table will give you an autonumber primary key for every part (PartID). Last, you will enter your tests using the PartID from the Parts table.

If you upload what you have so far, I can give more specific guidance.

 

[CuriousGeo]

I think your form will actually be multiple forms. But that's a discussion to have after you get your tables structured properly. From what you've described you should have 3 primary tables: Tissues, Parts, Tests.

You will then work in that order entering data. First you enter Tissue data into the Tissues table, every tissue will get its own autonumber primary key (TissueID). Next, you will enter every part of a tissue into the Parts table along with the TissueID of the tissue it belongs to. This table will give you an autonumber primary key for every part (PartID). Last, you will enter your tests using the PartID from the Parts table.

If you upload what you have so far, I can give more specific guidance.

Thank you for explaining steps for me. However, I don't understand why I would need to build a tissue table-the doctors would do that by entering the case accession number, which I made the primary key in my order table.

I have a parts table and test table made-to be used for lookup fields. I made an order table that serves to create and order. On this table, the doctor selects the date, enters the case accession number, selects his name, and then ultimately I need to add in the ability to add parts with tests.
For example, case 12-12345 has Parts A, B and C. For Part A, Tests 1, 2, 3 are needed. Part B, Tests 3, 4 needed. Part C, Tests 2, 8 needed.

 

[plog]

In general, you only have 1 'primary' table so far--tblOrder which is what I called Tissues (from now on I'll use your name). You still need tables to function similar to what I called Parts and Tests.

Let me take your structure table by table:

tblOrder This looks good and is the top-level of your primary tables. My only questions are about the AccessionNum field. How does it get assigned? Will only one pathologist ever be assigned to it? Will it ever contain alpha characters/ is it always numeric?

tblPart This table does nothing for you, I'd get rid of it. It essentially converts a letter to a number (A=1, B=2, C=3) and serves no real purpose. I know what you want to use it for, and there are other ways to accomplish it than with a table.

tblPathologist This table is fine.

tblStain This table is fine. I just want to make sure it lists all the Tests--is that correct?

You still need those 2 other primary tables (I called them Parts and Tests in previous posts). If it helps we can now call them tblTissueParts and tblPartTests. This is what those need to contain:

tblTissueParts:
PartID (autonumber primary key)
PartName (text field to hold A, B, C, D...)
AccessionNum (numeric field to hold which Accession it is from)

tblPartTests:
TestID (autonumber primary key)
TestNum (numeric field to hold ID field value from tblStain)
PartID (numeric field to hold PartID field value from tblTissueParts)

 

[CuriousGeo]

AccessionNum field. How does it get assigned? Will only one pathologist ever be assigned to it? Will it ever contain alpha characters/ is it always numeric?)

AccessionNum is assigned uniquely as the tissue case is brought to our lab. Yes only one pathologist is assigned, and yes it will always be numerical

tblStain This table is fine. I just want to make sure it lists all the Tests--is that correct?

Basically those are all the tests, but theoretically if a new test is brought in to our lab, we would need to add a new test.

 

[CuriousGeo]

tblTissueParts:
PartID (autonumber primary key)
PartName (text field to hold A, B, C, D...)
AccessionNum (numeric field to hold which Accession it is from))

So, would AccessionNum be a foreign key in the above named table?

tblPartTests:
TestID (autonumber primary key)
TestNum (numeric field to hold ID field value from tblStain)
PartID (numeric field to hold PartID field value from tblTissueParts)

Would PartID be a foreign key as well?

 

[plog]

Correct on both accounts. TestNum in tblPArtTests would be a foreign key as well.