Hey there,
I'm a research student with some experience in database creation, but nothing crazy. As a side to my actual research, I got stuck helping my lab organize their massive collection of data into one access database. I'm going to lay out what data I'm working with, where I'm at in the design, and how I think I should proceed (even though I don't know exactly how). If by then you can help me out, please do Bold italics are specific questions I have. This is long and complicated... sorry.
The Data
I work in a neuropsych lab that deals with brain cancer patients. This means in general, there are two broad categories of data: psych data (cognitive performance ect) and cancer data (pathology, treatment courses ect). With each of these, there are a multitude of dates corresponding to assessment dates, surgery dates chemo dates, and so on. I'm not even touching the cancer side of things yet - for one, its not a priority, and two, it's complicated (a lot is text entered paragraphs).
Moving on, in the psych side of things, patients come in for assessments at different points and complete a number of psych tests. The tests conducted (test A or test B or test C ect) may vary between assessment dates, but only slightly.
Where I'm at
The structure, pertaining only to the psych data, is as follows:
Subject_ID (one) - Patient_demographics (one) < DATE_INDEX (many) < (various assessments, 34 in total)(many)
1. The Subject_ID table is my master table, containing only the GLID (global identifier) and MRN (medical record number)
2. This is linked via a one-to-one with Patient_demographics through the GLID
- one patient has one GLID/MRN and one set of general demographic information
3. Demographics is linked to DATE_INDEX through the GLID
- in this table, I created a field called ID_EVENT, which needs to convey (GLID + Assessment date). It is a unique value that reflects a particular set of data for a specific patient for a specific date. Every record entry in subsequent assessments needs to be linked to this table via ID_EVENT. This will allow me to group assessments conducted on a date into one data row when I query patient data.
How do I set this up to:
a) auto-generate a unique number in ID_EVENT via an entry form/set up this relationship?
b) auto-fill this ID_EVENT only in assessments I want for this entry (via checkbox ect) and subsequently populate the GLID and DATE fields in these assessment forms associated with this ID_EVENT?
What I want to be able to do!
1. picture 1 shows my current form (with only a few assessments), I want to basically have picture 2 inserted at the arrow.
2. picture 2 shows the functionality at the form input level i want... click new event, select for which assessments, move on and enter into those...
3. pic 3 shows my relationships as of now. ignore the stuff on left and right (cancer data)