Page 1 of 6 123456 LastLast
Results 1 to 15 of 84
  1. #1
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46

    sub-sub form for neuropsych tests

    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

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This is all very confusing! Firstly, use the same terminology for tables, keys and explanations - demo same as demographic same as domain same as vet? The ID field name should match the table name, not just for our sakes but for all your future development. Once an ID has a name, use that exact name on all other tables (never link two tables with different field names).

    Second, it looks like you have a table for each test. This should be one table with a list of test names and then the ID used where needed. This may be where the issue lies. Read about normalization of tables and data, this is the basis of table design.

    Draw your table structure on a piece of paper, with lines (links) between tables. Make sure it follows all the normalization rules. This will help you get it sorted in your mind. Forms are then a simple matter of one form, one table, for data entry.

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  4. #4
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    Hm. Let me try this again:

    Demo is the table that has demographic info. Its primary key is DemoID.
    Exam is the table for day of exam data, its primary key is ExamID.
    A specific test, which is linked to the Exam table, is called by that test's name. So, for example, WAIS is an intelligence test linked to Exam table. The WAIS table has a primary key of WAISID.

    There is a one to many relationship between Demo and Exam, as well between Exam and a specific test (such as between Exam and WAIS).

    I don't really see how this is confusing or violates the usual norms for naming database objects...

    I will send a screenshot later of the relationships. To me this follows all the info I've read on setting up a database.
    Hopefully my screenshot will clarify what I've said thus far. Thanks for taking a look.



    Normalization requires avoiding rows and rows of duplicated or redundant data.

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    So the user enters the demo, then adds a new exam, and for this there are multiple tests? You could have a listbox listing all the tests available, or a combobox, then the user selects which test and you show the appropriate subform depending on which test they select. If every test has completely different data then yes, a different table for each is okay. Otherwise one table containing all the fields would be easier with a TestID linked to a master list table.

  6. #6
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    Quote Originally Posted by aytee111 View Post
    So the user enters the demo, then adds a new exam, and for this there are multiple tests? You could have a listbox listing all the tests available, or a combobox, then the user selects which test and you show the appropriate subform depending on which test they select. If every test has completely different data then yes, a different table for each is okay. Otherwise one table containing all the fields would be easier with a TestID linked to a master list table.
    Yes, yes. Combo box - very good. Aytee, the million dollar tip I need though, is how to set up the form so that I can enter the test data along with a given exam and given patient. As of now, I don't know how to have the vetId and the ExamId automatically link to the TestID. Frustrating! I have a subform set up such that once you add a new patient, VetID links to the new exam ID automatically, but I don't know how to link to the new TestID. The test data therefore floats independent of the patient and exam; I'm stuck .

    One big TestID table is no good I think, because most of the fields would be empty, pointless.


    Can I attach my database here, or is that not allowed? i'm on a different computer right now, but can send the screenshot tomorrow. I had a client today, but not tomorrow.

    Thanks!

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    never link two tables with different field names).
    Respectfully disagree. Linking tblCust.CustID with tblOrders.CustID_FK would be common practice I would think.
    To post a db, it usually has to be zipped. Suggest you compact yours first. Find the Go Advanced button next to Post Quick Reply. Attachment button is at the top of the upper toolbar in the post window. Add File, then Browse, then upload. It will go where the cursor was in your post when you start the attachment process.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46

    sample simple db

    Quote Originally Posted by aytee111 View Post
    So the user enters the demo, then adds a new exam, and for this there are multiple tests? You could have a listbox listing all the tests available, or a combobox, then the user selects which test and you show the appropriate subform depending on which test they select. If every test has completely different data then yes, a different table for each is okay. Otherwise one table containing all the fields would be easier with a TestID linked to a master list table.
    simple db data entry not working.zip

    Ok, I've attached an extremely paired down version of my db. All actual data are placeholders, not real subjects. These must be deidentified. Anyway, There are many fields in each table I left off, because the issue I'm trying to solve involves the primary keys.

    This db includes the following tables: Demo, Exam, and RBANS and WAIS. The last two are specific, very well known neurocognitive measures. I used to have the tables RBANS and WAISIV connected to the table Exam, but I deleted the relationship between Exam and WAIS (but not between Exam and RBANS), because I am trying to simplify this db to examine this one data entry problem I'm having. Later, WAISIV will be re-linked to Exam, of course, with many other specific test tables.

    My problem is this: when I add a new client, it knows that VetID (from Demo) and ExamID (from Exam) are automatically linked. But you can see in my master data entry form (FormDemoWorkingProblem) - that if you add new data for the RBANS, the VetID and ExamID fields remain blank. I have experimented and can't find a way to add new specific test data that is autolinked to the other tables. The RBANS data remains untethered to the person and exam date it should be connected to.

    Does anyone have a way to make the data entry work? Such that a given client (VetID from table Demo) and a given exam date (ExamID from table Exam) also link to RBANSID and WAISIVID and all the rest of the specific tests? Feel free to edit the db yourself, if that's allowed. Any suggestions are welcome. I know it can be done; I don't know how though.

    If I can figure out how to autolink RBANSID with ExamID, then I can do it over and over to other specific tests. Because not every client will take the WAIS or the RBANS, it would be bad design to just put all the data into Exam. 95% of it would be empty fields. Test batteries vary, so I will use Aytee's good idea on using combo boxes for each test, that's a great idea.

    All suggestions/edits are welcome.

    Thanks
    Steve

  9. #9
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Although you have created physical tables and are asking readers about your links between tables, the relationships between tables are really a reflection of your business facts. So to comment on your database structure requires us to know your business rules. It would be helpful to readers and you if you would describe "a day in your neuropsych business". Start at 30,000 ft level and gradually add some detail so we can see where patient, exam and test(s) fit and how they relate to each other in business context.

    A quick google shows these are tests:
    Wechsler Adult Intelligence Scale is an IQ test
    Repeatable Battery for the Assessment of Neuropsychological Status

    Good luck.

  10. #10
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    Quote Originally Posted by orange View Post
    Although you have created physical tables and are asking readers about your links between tables, the relationships between tables are really a reflection of your business facts. So to comment on your database structure requires us to know your business rules. It would be helpful to readers and you if you would describe "a day in your neuropsych business". Start at 30,000 ft level and gradually add some detail so we can see where patient, exam and test(s) fit and how they relate to each other in business context.

    A quick google shows these are tests:
    Wechsler Adult Intelligence Scale is an IQ test
    Repeatable Battery for the Assessment of Neuropsychological Status

    Good luck.

    As I wrote above, i give neuropscych tests and wish to create a db for them. A given patient has more than one exam date, and each exam date has many, many tests given. Tons of raw data.


    I am curious if anyone on the board knows how to use forms in a manner that enables a given person and their exam date to link up automatically when adding new test data in a form. The problem is, how to create a form that enables smooth data entry for these various tables. A simple subform allows the exam date and demographic table to be linked when adding data. But I don't know how to create the form such that a given, specific test matches up automatically with the other tables.

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    So, at 30000 ft,

    Male patient "Joe" , DOB 23-Apr-1975, arrives to be given an Exam. The Exam is on DateX (TimeT) and is for TestType RBANS.

    Now what?
    (other tests?, record results???

  12. #12
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    Exactly. So the tests given to a person vary quite a bit. One person may get the RBANS, one may get the WAIS. I usually given around 20 or more tests. Very involved. Much raw data.

    I need a way to set up a clean, efficient form to allow my neuropsychologist peers to enter all of this data for each person, date of testing, and all of the specific test data.

    that's why I figured we have a few one-to-many relationshps; between Demographic data (a table called Demo) and the hub, the Exam table, as well as between a given date of Exam and all the many specific tests that associate with a given date of Exam.

    The problem I'm having is how to set up data entry for that basic structure. If you were given RBANS and WAIS, but Charlie was given RBANS and the MMPI 2RF (a personality/mood test), then I need to have the specific tests given link up to the Demo table and the Exam table.

    The variable nature of the specific tests given indicate to me that each specific test should be its own table. However, Demo is the parent with a child table for Exam specific data such as date of exam and provider. That Exam table links up to the numerous specific tables for a given date of testing.

    It would be wrong to say link RBANS table to Demo, because I need to associate all the test data to a given exam date. I do many re-evaluations, so the real hub here is date of exam. One exam date for Charlie may have x number of tests, whereas Charlie's second eval has a different set of y tests.

    So, I think the structure is pretty basic, a given patient in the table Demo ---> has multiple Exams ---> and each Exam has multiple specific tests.

    But, does anyone know how to accomplish this in terms of data entry? No one seems to know yet...

  13. #13
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It's the
    and all of the specific test data.
    that is unclear. Don't be too quick to jump to physical tables. Make sure you identify the process and the data involved with the process(es) to understand (and communicate) the requirements.

    So working with Joe,tell us a little about all the tests and data. Remember, we only know what you tell us.

    I'm sure Joe can take several tests. We also need to clarify Exam and Test (what's the difference)?

  14. #14
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    I don't understand the question.

    A given date of testing is a different thing than the tests that might be given on that day.

  15. #15
    neuropsychresearch is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2017
    Posts
    46
    I have conceptualized the Exam table as a hub. Exam has demographic variables such as gender, provider, mode of eval (such as whether in person or via television), referral question, nature of eval (for capacity eval or outpatient neuroposych?) and other such things. It also contains specific test foreign keys such as RBANS ID and WAISID that link to the data located in the specific tables.

    Clearer?

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. How long it took to pass tests and to get to the highest level?
    By aellistechsupport in forum Programming
    Replies: 3
    Last Post: 05-30-2014, 01:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums