Results 1 to 9 of 9
  1. #1
    hoff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    7

    Question Best way to manage multiple events for individual patients (research DB)

    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)

    Click image for larger version. 

Name:	form1.jpg 
Views:	20 
Size:	53.1 KB 
ID:	7759Click image for larger version. 

Name:	event_form.jpg 
Views:	19 
Size:	68.0 KB 
ID:	7760Click image for larger version. 

Name:	relations.jpg 
Views:	20 
Size:	116.9 KB 
ID:	7761

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    First, if Subject_ID and Demographics are 1 to 1 and every patient will have one record in both tables, why not just one table?

    Second, will require VBA code to generate the unique ID_EVENT value or use GLID and date as compound key or autonumber.

    Use form/subform/subsubform arrangement to synchronize the DATE_INDEX data entry with the Subject_ID table and also with the assessment tables which I presume are the assorted tables at bottom of pic 3. Can patient have more than one record in each assessment table?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    hoff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    7
    1. Yes, I had brought that up with my boss before and he preferred having MRN separate from name, but I've now combined it.

    2. I'm going to try to make a compouned key of date and GLID

    Quote Originally Posted by June7 View Post
    . Can patient have more than one record in each assessment table?
    Yes, each record would represent a different assessment date. This is why I want to have a continuous (between tables) value separate from GLID in each assessment that represents the assessment date for that GLID.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    Having multiple assessment records in each table for each patient will probably mean subreports to output all assessments in a single report object. Am I seeing at least 23 assessment tables? What if you need to add a new assessment category?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    hoff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    7
    Quote Originally Posted by June7 View Post
    Having multiple assessment records in each table for each patient will probably mean subreports to output all assessments in a single report object. Am I seeing at least 23 assessment tables? What if you need to add a new assessment category?
    There are 24 assessments currently. These cover every possible assessment out there, and previous versions in some cases. Most patients will have 5-10 of these, the rest are there just in case. If new assessments (new table + form) are added in the future, they could be linked back to the date index and added as a subsubform the same way I'll have to link current tables.

    If there is a new assessment that needs to be added, it shouldn't affect any present data because these changes are rare (a new assessment version every 10 years maybe) and the usage of a new assessment form in a clinical setting won't occur before it's added to the db. Even if it does occur, data entry occurs much later than initial collection..sometimes months. There will be time to make these changes if needed.

    As for the relationships... I now have one table for subjectID + patient demographics that's my master table with GLID as the primary key. It is linked via one-to-many to Date_Index through GLID. I have GLID and Date_ID as the compound key in Date_Index and I'm getting rid of Event_ID.

    In subsequent assessment tables, should GLID and Date_ID also be a compound key? And I would link these to Date_Index through both (GLID + Date_ID) in a one-to-one?

  6. #6
    hoff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    7
    this gives you a better view of assessments. like i said, the stuff on the left and right are the things I'm not focussing on. The assessments I'm looking at are the 24 below date_index

    Click image for larger version. 

Name:	relations.jpg 
Views:	20 
Size:	127.9 KB 
ID:	7771

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    Although I guess they work just fine, I don't like compound keys and try anything to avoid them. I have one table that would have meant 5 fields as compound key. Instead, I use an autonumber field (the only one in the entire db) as the primary key and control prevention of duplicating the 5 fields with form setups and VBA code. In another project I do have two fields set as a compound primary key to prevent duplication of the pairs and table links require both fields as pk/fk. The only time I ever resorted to compound key. On reflection, the only reason for linking to this table is to lookup a name value. There are no other fields in the table and the name values are mostly short enough I could have just saved them to the primary table and not worry about linking in queries to retrieve for output.

    So combination of GLID and Date will be unique in Date_Index? Could allow autonumber field to generate a unique ID primary key and use that as the foreign key in each of the assessment tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    hoff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2012
    Posts
    7
    Yeah, the idea of Event_ID is that it is a unique event representing a combination of GLID and Date...

    so you suggest having an autonumber Event_ID field as the primary key in Date_Index?

    the main function I need is: every assessment for a particular date and GLID will fall into one row when doing a query. the main function of this database is to export data to SAS or SPSS programs for analysis in xml formal, and the better organized before export the easier it will be.

    ex.

    GLID DATE EVENT ASSESS1.... ASSESS2 ect..
    1...... jan 1..... 1.......data(event1)..data(event1)...
    1...... jan 2..... 3.......data(event3)..data(event3)..
    2...... jan 1..... 2.......data(event2)..data(event2)
    2...... jan 2..... 4
    2...... jan 3..... 5
    3...... jan 6..... 6

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,445
    That would be my preference.

    I particularly don't like using dates (or names) as key values.

    However, I understand your concern about accidental duplication of GLID/Date pair.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-25-2012, 04:13 PM
  2. Multiple events for a individual
    By nevets in forum Database Design
    Replies: 2
    Last Post: 02-18-2012, 12:54 PM
  3. Research sources
    By jgelpi16 in forum Programming
    Replies: 8
    Last Post: 02-04-2011, 07:25 AM
  4. Replies: 3
    Last Post: 01-25-2011, 09:50 AM
  5. Multiple events per location Distance tables
    By flebber in forum Database Design
    Replies: 1
    Last Post: 03-13-2010, 08:48 PM

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