Hi there. I'm a complete novice trying to create my first database, so please feel free to treat me as such.
I'm trying to track specimens coming through my lab, which will go through several steps of a long procedure. We want to track on what date each step happens for a specimen, and each specimen might go through each step multiple times (if say, a run fails), so I have separate tables for everything: Specimens, Extractions, PCR, Sequencing.
Each of those tables is linked to the next through a key -- each specimen can have multiple extractions, each extraction can have multiple PCRs. In the PCR table, I've built a combo box for people to select the Specimen ID. My question is this: How, after adding a Specimen to the PCR table, do I restrict the Extraction ID column to only those that match the selected Specimen ID? I'd prefer for the Extraction ID field to auto-populate with the latest entry for that Specimen ID selected, if possible.
I hope that's clear. See the picture of the relationships, it might help make more sense. Thanks in advance!