Hello everyone. I am creating a simple database for neuropsych research. Each patient has multiple examination dates, and on each date they receive MANY tests - upwards of 15. Plus, each test has a raw score and a psychometric score (Z score, etc).
I am thinking of the database architecture as this:
A Demo table with unchanging demographic info such as date of birth and handedness. Primary key is VetID (autonumbered). TblDemo is in a one to many relationship with tblExam. tblExam involves day-of-testing info such as Provider and eval mode (whether in person or via telehealth). This table has the autonumbered ExamID. Each exam should really only have a one-to-one relationship with a table dedicated to a neuropsych domain, but I have it in a one to many as of now. So, for example, a given exam date will have an intelligence table that is linked to it via ExamID. The specific table for intelligence, however, is called Intelligence and has an autonumbered unique ID.
I have linked tblDemo to tblExam via VetID to ExamID. So far, so good. Furthermore, IntelligenceID in tblExam is linked to the primary key of IntelligenceID in tblIntelligence.
This part I understand. But here's the problem I'm having:
I have MANY tables for specific tests (RBANS is another famous test, with RBANSID as primary key, for example) that link to the RBANSID in tblExam.
I need to create a form that allows data entry in a simple way for ALL of these tests. I have created successfully a form and subform based on tblDemo and tblExam that allows the user to enter the demographic data and exam day data for a given patient.
The problem is: I am struggling to figure out how to also simultaneously enter the specific test data. When I execute my Form, it doesn't know to link the specific intelligence test data with the appropriate exam. On Youtube, Dr. Veerschuuren recommends using a query to create a sub-sub form. I followed his model and it still doesn't work.
This nested structure is proving difficult for me to implement in a form. I have clumsily resorted to a Lookup field in the form to let the user pick out the ExamID for a specific test! I know this is lame. I AM able to link up the specific intelligence data to tblDemo, though.
Any suggestions? I've hit a wall. How to enter nested data via forms? Or, another way to say it, I know how to create a sub-form but I don't know how to create a sub-subform. I'll gladly provide additional info if need be.
Thanks
Steve