Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43

    Clinical trial with 900 unique questions for 200 unique patients

    I am seeking advice for building a database for a clinical trial in surgery and medicine.
    --
    Project:
    --


    Approximately 200 patients are going to answer more than 900 questions split out on different questionaries they get handed out as printed paper. They do not answer all the questionaries at once, but at seven different visits at the doctor, the last being six months after surgery. All these papers needs to be recorded into a database as data, and later exported as tables to an statistics program called SPSS.

    With MS Access it is possible to make an navigational form with one pane or subpane for each of the different questionaries to be recorded about the patient. It has then been requested that all data is visible from one form only, and with the following structure:

    To the left: An identification-area where the patient with his/hers inclusion number, and other static values like "gender" and "birthdate". The inclusion number -field needs an search function, so it is possible to find and continue recording on patients already in the records (already in one or mere tables). It is not practically possible to have all data about one patient ready at the same time.

    To the right: Different navigational panes (one for each questionary). It is crucial that these panes are all connected to the identification-area to the left, so data from one patient stays connected to the excact same patient.
    --
    Design idea:
    --
    My idea is to first make one tabel for each questionary, with question names as field names, then an autonumber field (QuestionaryAnswerID), and then one identification field to connect records from all the different tabels to one patient (the patients inclusion number).

    Then I make the navigation form with panes for each tabel, and finish the form-panes off by making some predefines answers as dropdown menues on those fields that allow predefined answers (all numerical/boolean).

    thanks in advance
    T

  2. #2
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    You don't mention the nature of the questions: are they free-answer text that has to be coded by reference to a frame; are they closed-choice; are they scaled-rating; etc.? The answer to these questions does not significantly alter the db design but will affect the complexity of the application.

    You should start with the entities and their relationships rather than the GUI. It's OK to have an idea about panes and so on toward which you are working but the db design is most important at this stage. I foresee the following entities.

    Patient. This one's fairly obvious and I won't describe it any further.

    Questionnaire. There will be seven records. Typical attribtes will be name, sequence, etc.

    Question. One attribute will be a foreign key to questionnaire. Other attributes will be sequence (question number), question display text (a precis for form display where the original text is too long), question type (maybe), etc.

    Answer. Maybe you want to choose another name but basically this is what the entity contains: the answers to the questions given by a specific patient. There will be two foreign keys: one to patient and one to question.

    OK, the relationships are straightforward: Patient-Answer, 1:M; Question-Answer, 1:M; Questionnaire-Question, 1:M.

    Now the difficulty is in 'interpreting' the code frame or closed-choice answers. It's all very well seeing a numeric value in the form but what does it mean? If you need to see the textual meaning of a numeric value then there has to be a child entity to the question entity that expands the numeric values into their meanings. You may need this anyway for all answer types if you wish to prevent out-of-range entries (i.e. validation).

    As you are using SPSS for analysis (is it still going - SPS from CRC was much better) I assume that the Access is only for data entry. A multi-pane form may be achieved through subforms (or perhaps a main form/subform(s) design). You will face a difficulty in synchronizing for new patients but this is a common situation and there is a spectrum of solutions for this. The seven questionnaires might be handled by a tab control if you need to hop from one questionnaire to another or they may be handled by an overall selection for each data entry session. For code frame and closed-choice answers you may decide to use a pop-up displaying all possible choices.

    My final thought is to ask if there are any multi-choice questions or any ranking questions? There are different again and need unique handling.

  3. #3
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    A postscript! There are already template Access applications for survey processing. I don't have any links but it will be worth your while searching for these.

    Orange, do you have any links in your list?

  4. #4
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    I deleted this post, because I happened to make a double post.
    Last edited by trolleri; 01-28-2013 at 01:17 PM.

  5. #5
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    I would really like such an application! I have searched, and will continue searching. If you find the application, please notify me.

    Types of answers:
    The answers in four questionaries are mostly a range of numbers, e.g. they choose one number fra 1 through 10 or 1 through 3 etc. But there is also yes/no answers.

    The answers in three other questionaries starts with two memo-fields, then there will be numeric data (like the heart rate), and yes/no answers.

    The answers in four other questionaries is mostly yes/no answers, but if "yes" then I need to able to record the written text which I cannot predict. There is also numeric values and memos (without yes/no) to på answered.

    One questionary have a great mix of yes/no, and if yes then written text. Theres is also simple questions like etnicity (caucasian, asian, other: [then text]). And there is predefined answers like NYHA-classification (1 to 4).

    All questionaries need a date recorded for when it was answered by the patient, and when it was recorded in Access and who it was recorded by. Furthermore the date fields are generally scattered out on all questionaries to be answered by the patient. E.g. "When did symptom one start, and when did this symptom possibly stop".

    Design:
    Is it correctly understood, that every questionary have its own table, where the columnnames signify a question, and the patients answers then gets recorded as rows underneath? One row for all the answers from one patient.

    Then for the answers where I want to create predefined answers; I will create another table where the columnnames signify a question, and the rows in those column then contains my predefined answers for excactly that columns question? I am not sure how to implement this into a form, but one problem at a time - first I want to create alle tables like you recommended.

    You can download an example of some of the questionaries here: dropbox.com/s/6l2kqmvxbdd6drv/7.accdb
    Last edited by trolleri; 01-26-2013 at 10:30 AM. Reason: formatting

  6. #6
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I have put out some feelers for sample survey processing templates and shall also conduct my own search. My experience is that I have never been able to use a template as is and I am then faced with the decision as to whether it is less effort to customise a templat or start from scratch.

    My survey processing experience dates from the early '80s whem PCs did not exist and the number crunching was performed on mainframes. It is possible that I am a little out of date so if things have changed, bear with me.

    It is straightforward to deal with binary (yes, no, don't know; agree, disagree) and scale (e.g. 1 = v. bad, 2 = bad, 3 = neutral, ...) answers. It is quite easy to program logic such as "If yes, then ..." into the application.

    The complexity occurs when handling other types of question/answers. From memory they are:

    1. "Which of the follwing statements ..." (Statements are numbered.) The complexity occurs here when the respondent is given a choice of "None of the above" and is then asked for his/her own statement. This is the same as an open ended question - see number 2 below.
    2. An open ended question/answer where the interviewer records (exactly?) the answer of the respondent. What happens here (used to happen) is that the survey executive reviews a sample of questionnaires and compiles a code frame, grouping answers that are deemed to be similar. Each group is assigned a number and then coders assign the appropriate number to each and every questionnaire. Sometimes it is necessary to extend the code frame as coders encounter answers that do not fit into the original grouping.
    3. Muti-answer questions that may be of varying types. One variant is to order (priotitise) certain statements from 1 to ... Another is to choose more than one statement, unranked. And so on.


    1 & 2 above do not really affect the design of the survey processing application - all work is done prior to data entry - except that it must cater for a code frame if the numbers are to be matched agaist text. The multi-answer often explodes into a set of single answer questions; otherwise the application has to cater for multiple values.

    If I understand you then Access is to be used only for data entry and SPSS is to be used for the number crunching and output. The Access db design therefore should be as compatible with SPSS as possible. I suggest that it would be easier to see the SPSS design before making any Access design decisions.

    Enough of the theory, here are some specific comments.

    ... I need to able to record the written text which I cannot predict..
    No problem but how will this migrate to SPSS? The actual mechanics of the data entry might mean that the text answer is typed into a pop-up - lack of screen real estate.

    All questionaries need a date recorded for when it was answered by the patient, and when it was recorded in Access and who it was recorded by. Furthermore the date fields are generally scattered out on all questionaries to be answered by the patient. E.g. "When did symptom one start, and when did this symptom possibly stop".
    No problems.

    Is it correctly understood, that every questionary have its own table, where the columnnames signify a question, and the patients answers then gets recorded as rows underneath? One row for all the answers from one patient.
    This will work for a straightforward single-answer questionnaire but it will contravene the rules of normalisation. As I indicated above the Access db design should be that best suited for migrating the data into SPSS so, perhaps in this instance, normalisation is of lesser importance. Let me just say it's not the way I would design it.

    Then for the answers where I want to create predefined answers; I will create another table where the columnnames signify a question, and the rows in those column then contains my predefined answers for excactly that columns question?
    Doesn't sound like a consistent design to me. I'll make a separate post showing how I would design this. I'll also keep you up to date with any application templates that we find.

    Meanwhile I will also look at the example you quote.

  7. #7
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    I've had one suggestion:

    http://www.rogersaccesslibrary.com/forum/topic3.html

    It may be worth a look.

    Meanwhile here is my skeleton db design for a survey processing application. (Click to enlarge.)

    Click image for larger version. 

Name:	1.jpg 
Views:	66 
Size:	50.5 KB 
ID:	10890

    In your case Respondent = Patient, Interviewer = Doctor and so on.

    The complexity of the design is in the CodeFrame concept and I would tackle this by using an Object Oriented approach: the code frame points to an object instance that itself points to a pop-up entry form, etc., etc. (The object instance even designs the form if it does not exist!)

    I feel however that I may have a different objective from you in that I naturally think in terms of the 'ideal' solution - the most generic and reusable solution - whereas I suspect you need a solution for this situation and will probably never get involved in survey processing again. Much as I regret saying this, it may be more productive for you to use Excel. Have you considered this?

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I'm not sure if this is what you are looking for or that it will help, but Duane Hookom has provided an example survey dB:
    At Your Survey
    http://www.rogersaccesslibrary.com/f...ey_topic3.html

  9. #9
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Might be worth while to look at this, not that it specifically applies.

    https://www.accessforums.net/sample-...zes-13082.html

  10. #10
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    Thank you everyone, and especially you Rod!

    I have looked at the two example-applications and find them very interesting - I definitely can learn a lot of them! But I think you are right Rod, when you say it might be quicker for me to design this specific database myself from scratch.

    Quote Originally Posted by Rod View Post
    I suspect you need a solution for this situation and will probably never get involved in survey processing again. Much as I regret saying this, it may be more productive for you to use Excel. Have you considered this?
    I will get into survey processing again, since I will do more research myself, and hopefully a full PHD later on. But I have truly considered Excel for this specific task. Excel does however not give the possibility to create an easy form for entering data, and an Excel document with almost 1000 columns and more than 200 rows would be insane to browse through when entering data - regardless of how easy it is to import into SPSS.

    Quote Originally Posted by Rod View Post
    As I indicated above the Access db design should be that best suited for migrating the data into SPSS so, perhaps in this instance, normalisation is of lesser importance. Let me just say it's not the way I would design it.
    This is exactly the case, it is paramount that SPSS can import the data. SPSS does import tables easy, but I find normalization hard to comprehend (and make) for this solution.

    Quote Originally Posted by Rod View Post
    If you need to see the textual meaning of a numeric value then there has to be a child entity to the question entity that expands the numeric values into their meanings. You may need this anyway for all answer types if you wish to prevent out-of-range entries (i.e. validation).
    I wonder if the restriction for data-input (ranged questions, yes/no questions etc.) should be done in the forms, and not on the table-level. My approach was to have one table per questionary, with the patients answers recorded as rows in those tables - this is for easy export. Then I already know how to make most of the restriction on the form-level through combo boxes, but i would rather nok remake the entire database or form, if something proves to be wrongly designed to begin with. Read my concern underneath.

    Quote Originally Posted by Rod View Post
    You will face a difficulty in synchronizing for new patients but this is a common situation and there is a spectrum of solutions for this. The seven questionnaires might be handled by a tab control if you need to hop from one questionnaire to another or they may be handled by an overall selection for each data entry session.
    I am not sure which of the two solutions given is the best, but my main consern is this: Is is possible to create a refresh on all tabs when searching for another patients ID in the identification-area of the form? I do not want the form to just replace the former patients ID with the one I am searching for.

    When we are going to enter the data (records it in the database), we will ony have bits of the full datapiece for a patient, e.g. all the answers for "Questionary A" from "Patient X", and later the answers for "Questionary B" will be ready. All the questionaries on paper form (handwritten) does however get stored in folders under the patient ID. That is why it is necessary for us to able to search on a patient ID, and then see what have been entered, and what still needs to be entered. If no patient exist with the entered patient ID, it should be possible to create a new record.

  11. #11
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    This is exactly the case, it is paramount that SPSS can import the data. SPSS does import tables easy, but I find normalization hard to comprehend (and make) for this solution.
    I wonder if the restriction for data-input (ranged questions, yes/no questions etc.) should be done in the forms, and not on the table-level. My approach was to have one table per questionary, with the patients answers recorded as rows in those tables - this is for easy export. Then I already know how to make most of the restriction on the form-level through combo boxes, but i would rather nok remake the entire database or form, if something proves to be wrongly designed to begin with.
    Yes, your idea will work. By placing the validation in the form (rather than in a code frame table) you are in effect migrating the code frame(s) to the form. The consequence is that you then need a new, different form for each questionnaire. Nothing wrong with that except the tedium of creating new forms and validation for each new survey.

    As far as the 'Table -> SPSS' mechanism is concerned you seem to be thinking that the source table must be one of your permanent db tables. Queries - sometimes called 'views' in other DMSs - can usually create temporary tables in exactly the layout required by the target system. This feature allows the Access designer to concentrate on the most efficient, best normalised design for the front end (data entry) application. I know I said the Access db has to be compatible with SPSS. Once feasibility is proven, the debate is whether to sacrifice some of the reusability features of the design in order to make the migration to SPSS as easy as possible - in more practical terms, whether to sacrifice the adaptability of the Access design in order to avoid writing and maintaining queries for the migration.

    I know little about SPSS, particularly how it handles Boolean/binary choices. I am assuming the choices are stored as numbers. E.g. 1 = Yes, 2 = No, etc. There's no particular problem if however they remain as binary.

    I am not sure which of the two solutions given is the best, but my main consern is this: Is is possible to create a refresh on all tabs when searching for another patients ID in the identification-area of the form? I do not want the form to just replace the former patients ID with the one I am searching for.
    Be assured that if the subordinate/child data contains the key of the patient then Access will handle the synchronisation as you move from patient to patient at the main form/parent level. This really saves much programming effort but the difficulty occurs when entering a new patient (if you allow it) in the main form. Now there is, as yet, no patient record in the underlying table. (It's created when the new data is first saved.) While there is no parent record any attempt to add child records will result in an error. Most of the time the sequence of db updates happens such that the error does not occur but it is as well to be aware that the error can and does sometimes happen.

    My idea about a tab control was to have a subform, one for each questionnaire, on each page of the tab control. As I explain above, Access can synchronise all the subforms simultaneously. If the synchronisation processing is involved then there may be a performance hit when moving from patient to patient but I do not anticipate this in your case.

    When we are going to enter the data (records it in the database), we will ony have bits of the full datapiece for a patient, e.g. all the answers for "Questionary A" from "Patient X", and later the answers for "Questionary B" will be ready. All the questionaries on paper form (handwritten) does however get stored in folders under the patient ID. That is why it is necessary for us to able to search on a patient ID, and then see what have been entered, and what still needs to be entered. If no patient exist with the entered patient ID, it should be possible to create a new record.
    I'm not sure sure what you mean by this and why you feel it's significant. Questionnaire responses (answers) may be built up progressively over time by revisiting each patient as many times as needed. Patient data, likewise, may be completed over time. I would not anticipate that you use the same form for patient data that you use for entering survey data. This actually overcomes the problem I have discussed about missing parent records; you disallow entry of new patients in the questionnaire form and provide a mechanism that jumps to the patient data form when you encounter a new patient.

    Your requirement has sparked my interest. I shall play with your ideas and may even publish a skeleton db.

  12. #12
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Here's a very crude proof of concept db for designing the processing following your ideas. The controls with dark orange backgrounds are supposed to be hidden but I've allowed them to be visible for clarity.

    MSAFtrolleri.zip

    BTW, if you post any examples please make them available for v2007 or earlier.

    PS Your scheme has the disadvantage that you are restricted to 64 characters for control labels (= a question).
    Last edited by Rod; 01-29-2013 at 11:26 PM. Reason: Added PS

  13. #13
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    You gave me a lot to work with, thank you!

    I will look through it all, and keep you posted on my progress.

    Have a nice weekend

  14. #14
    trolleri is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2013
    Posts
    43
    Your example really showed me how to make parent-child relations! Thank you!

    This is my most recent draft: https://www.dropbox.com/s/4e64y46zji...tions.mdb?dl=1


    PatientID
    CPR equals the Danish social security number, and is an unique number value with the mask XXXXXX-XXXX. This is the number we tie all questionnaires to, and end-users identify the records by this number. I could not make one-one relations for this field, since it isn't an autonumber in tblPatients, so I made the field PatientID in each questionnaire instead. Is it necessary to crosscheck the values in the CPR field with the values in the PatientID field? I anticipate that end-users will never have to see the values of the PatientID field, since it is created and handled by the database. Maybe I should delete the field CPR for each table other than tblPatients, and only use the field PatientID as the identifier between the tables.

    I like the way you made a dropdown box for switching between records, how did you do that?


    Predefined answers
    How excactly do I set these up? I have been looking at your sample design, and it looks like you use modules. These look complex. I also looked at your posted picture, where you create very few fields in tblQuestion and tblAnswer but with room for lots of records in them. If I duplicate this idea, I will need to rearrange my tables from having the quistion IDs as fieldnames. Is that worthwhile? I just want to know, before I make the rest of the tables.


    When the predefined answers are Boolean with a text e.g. Yes/No, how do I set it up so the records save the input as a binary value. E.g. male = 1 and female = 2. I want the end-user to see the Boolean text, not the binary number.


    Quote Originally Posted by Rod View Post
    ...you disallow entry of new patients in the questionnaire form and provide a mechanism that jumps to the patient data form when you encounter a new patient.
    I am very interested in how this navigation mechanism is created


    Navigation
    Is it possible to make sub-subpanes? The structure outlined:


    Visit 1
    - CRF (Investigator notes) 1
    - Holter records 1
    - Quality of Life 1
    - - Parameters 1
    - - State of health (SF-36) 1
    - - The Symptom Checklist 1
    - - Performance: Activities of Daily life 1
    - X-rays
    - Measurements 1
    - - Conversation 1
    - - EKG 1
    - - Blood pressure 1
    - - etc 1
    - Blood samples 1
    Visit 2
    - CRF (Investigator notes) 2
    - Measurements 2
    - - Conversation 2
    - - EKG 2
    - - Blood pressure 2
    - - etc 2
    - Blood samples 2
    Visit 3
    - CRF (Investigator notes) 3
    - Holter records 2
    - Quality of Life 2
    - - Parameters 2
    - - State of health (SF-36) 2
    - - The Symptom Checklist 2
    - - Performance: Activities of Daily life 2
    - Measurements 3
    - - Conversation 3
    - - EKG 3
    - - Blood pressure 3
    - - etc 3
    - Blood samples 3
    Visit 4
    - CRF (Investigator notes) 4
    - Holter records 3
    - Quality of Life 3
    - - Parameters 3
    - - State of health (SF-36) 3
    - - The Symptom Checklist 3
    - - Performance: Activities of Daily life 3
    - Measurements 4
    - - Conversation 4
    - - EKG 4
    - - Blood pressure 4
    - - etc 4
    - Blood samples 4
    End of Study ("CRF 5")


    As you can see, there is a lot of subpanes, and those really need to have the above sub-subpanes for them to be manageable.
    Furthermore I might need to be able to create more subpanes later if it proves to put things in better order, e.g. Blood Sample Standing, Blood Sample Baseline etc.

  15. #15
    Rod is offline Expert
    Windows 7 32bit Access 2007
    Join Date
    Jun 2011
    Location
    Metro Manila, Philippines
    Posts
    679
    Get back to you asap - somewhat busy for a couple of days.

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

Similar Threads

  1. Unique queries - Unique Count
    By bigfish in forum Queries
    Replies: 7
    Last Post: 03-14-2012, 01:28 PM
  2. Creating unique ID's
    By quietmortal in forum Queries
    Replies: 1
    Last Post: 09-01-2011, 02:29 PM
  3. Unique attachments
    By twalishuka in forum Programming
    Replies: 1
    Last Post: 02-28-2011, 09:28 AM
  4. how to set a unique ID
    By archie in forum Access
    Replies: 1
    Last Post: 09-08-2009, 04:28 AM
  5. Unique Puzzle
    By Christina G in forum Database Design
    Replies: 0
    Last Post: 03-14-2009, 06:37 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