(newbie) Can't find how to set up my relationship for clinical database with followup

[nightale]

Hi guys,

I am a graduate student and I'm "trying" (very hard though) to create a relational database in Access for my thesis study.

Basically, I have recruited patients that I evaluated with a battery of tests and questionnaires.

I'm having trouble to set my table relationships so I have the possibility of recording follow-ups if I need too (although I don't plan to do it in a near future, I want this feature enabled as I might use the database template for other studies).

Basically, I'll have 80 patients and they'll be each recorded in my "demographics" table (tbl_Patient).

Each of them will undergo an extensive battery of tests and questionnaires. I have created 3 tables for 3 of these (GDS15,DRS2,APATHY).

Every patient will at least be evaluated at baseline (T0). I may do some follow ups with all or a part of my battery later on (T1, T2, T ...).

I'm a newbie and I'm completely lost in all this relationship process, even though I read a lot of them. I know I need a junction table for my baseline/follow up problem. I created one "tbl_PatientTime" ... but I'm kinda lost on how to set the "tbl_Time"... (see picture attached below)


Please see that link for a screenshot of my actual database relationships figure (had to cut the www part so I could post, couldn't insert image as I don't have over 10 posts...)
dropbox.com/s/mq0jarabkhsxa3z/access_help.jpg


If it's easier for you, here's a link to the database (at least what I've done so far)
dropbox.com/s/6agzj1i7btz5dyb/DOIRON_LABO_NPGv2013-11-20c.laccdb

PS: screen capture and database are attached in the message as well.

Thank you SO VERY MUCH for your help and pardon my english.

Regards,
Max

 

[jdraw]

Work though this tutorial. It will lead you through the process of identifying tables and fields (entities and attributes) and setting up relationships.

Good luck with your project.

Once you get your database designed --MAKE SURE YOU HAVE AN AUTOMATED BACKUP SET UP TO BACKUP YOUR DATABASE REGULARLY.

 

[nightale]

Hi,

Thanks for the help and the tips.

However, after going through this file (and also the previous normalization tutorial on roger's blog), I'm still lost with regards to some aspects of my database relationships.

The problem is that I can't seem to get my "test1" form to be synchronized with the "patient" form, i.e. when I'm typing data in my patient form, I'm creating patient IDs (e.g. 1,2,3,4). I can browse through the records and make changes to my patient table data. However, even though I set my relationships correctly (well at least I think so), I have to manually specify the patient ID for the test1 form to record for the correct patient.

I'll try to simplify things :

tbl_Patient
Patient_ID *Primary Key autonumber
Age
Sex
...
Many other demographic data
...


tbl_Test1
Test1_ID *Primary Key autonumber
Patient_ID
Test1score1
Test1score2
Test1score3
...
Test1scoreX



Because one patient can be evaluated on more than one instance with test 1, this gives me a one to many relationship. But because test 1 can be administered more than once to one patient, I have another one to many relationship. Thus, this give me a "many to many" relationship overall. Is that correct?

I then created a junction table.

tbl_PatientTest1
PatientTest1_ID *Primary Key autonumber
Patient_ID
Test1_ID


In my relationship diagram...

I've click-&-dragged Patient_ID from tbl_Patient (primary key) to the Patient_ID of tbl_PatientTest1 (thus making it a foreign key, right?). This gives me a 1 to many relationship.

Then, I've click&dragged Test1_ID from my tbl_Test1 (primary key) to the Test1_ID field of the tbl_PatientTest1 (thus making it a foreign key).

Thus, I now have a many to many relationship where one patient can be evaluated more than once on Test1...

However, I think I'm messing something up, because in fact the test_ID instance can only belong to one patient (one individual set of score = can belong to only one patient). So in this case, should I go with a simple 1 to many relationship between tbl_Patient and tbl_Test1 without using the junction table?

Also, how can I label each instance of the same test within one patient case as T0,T1,T2,T(n+1)?

Then, my problem is that even though I have entered 5 cases with demographic data in my tbl_Patient, the only way I can assign the results of Test1 to Patient1 is by adding a Patient ID field in my form and entering it manually... when looking at my tbl_test1, I should be able to navigate through my 5 records, no?

Thank you very much, I am very desperated to understand this and it's driving me crazy...

 

[jdraw]

Capture the screen of your relationships window, then zip the file and you should be able to post that.

It would be most helpful to readers if you would
-write a brief description, in plain English, of WHAT you are trying to do
-avoid Access terminology ---just plain English
-use a similar approach to that in the tutorial mentioned earlier

If a Patient can take a test many times, you will need tables for
-Patient (patient identity and coordinates)
-Test(unique test identifier, name, specifics of test..)
-PatientTest(junction table indicates Patient x took test 1 on date y, comments if necessary)

Since you have many Patients and many Tests, you have a Many to Many situation. This can be resolved via junction table. Here is a video tutorial re Many to Many resolution.

Is an evaluation different than a test?

You do NOT NEED a table for every test (at least not with the info so far). You identify the Patient, Test and DateOfTest to resolve multiple occurrences of Test for Patient.

I honestly think you should redo the tutorial. Normalization and keys are critical to understanding database design.

Good luck.

 

[nightale]

PS: by evaluation, I mean the whole battery. For each iteration of evaluation, the whole battery (or a part of it) was administered).

Thank you so much for your patience with me. I did re-read the tutorials and I have a hard time translating examples of the customer/order type to a clinical database. There might be a simple thing I don't get....

Here's a screenshot of my relationship diagram.

Basically, I will evaluate a lot of patients with tests and questionnaires. Their demographic and clinical data will be stored in the "Patient" table. Each patient will undergo an extensive battery of 16 tests and questionnaires at least once at baseline. A part of them will be re-evaluated a few times as part of a follow-up study.

For the moment, I only created 3 tables : Depression (my depression scale), Apathy (my apathy scale) and Dementia (my dementia scale). Within each questionnaire, there can be over 50 scores to record each time a patient fill a questionnaire. Thus, I decided to create one table by questionnaire because there are so many fields within only one test/questionnaire.

What I want to do : store all this data in a way my assistants can
1) create a new patient file record (or select an existing one);
2) once a patient is created in the system (by filling his demographic data), this will add automatically an empty "baseline evaluation" line for each and every test.
3) assistants will use tabs from a navigation form to fill the results and the scores of this patient in all the tests and questionnaires.

*If a patient already has a T0 (baseline level) recorded, this will enable the possibilities of recording a second set of scores for every test and questionnaire, but with the label T1 instead of T0.
(analogy = if a customer already has an order in the history (e.g. T0), this will create a second order, now labelled T1, then T2, then T(n+1))

I hope this is a better explanation. I thank you very much for your time.

Regards,
Max

 

[jdraw]

OK.

I don't think your Patient Table is set up properly. You have a number of physical attributes eg height, weight, bmi, marital status... that could change during your study.

If you are going to conduct 16 tests over say a six month period, and you may repeat some tests, you may wish to put these in a separate,related table and include the Date. Failing that you might consider an AsAtDate in the Patient profile.

I do not understand "questionnaire". Can you tell us more about these 16 tests and questionnaires? Perhaps you could lead us step by step through a test/questionnaire with Patient John Doe.

tblApathy, tblDementia and tblDepression should NOT include PatientID or Totals field. As I read you model, these are attributes of Apathy, Dementia and Depression regardless(independent) of Patient.
You relate a Patient with an ApathyId in your tbl_PatientApathy ---same with tbl_PatientDementia and tbl_PatientDepression tables.

When I see Pat_Apathy_1,Pat_Apathy_2...etc it cries out for Normalization, BUT readers do not know the details of your "business" and you haven't provided many. So my comments are just guesses at this point.

 

[nightale]

Thank you for your time again.

I agree with the patient demographics. I will only put information that won't change in this table, such as Name, Date of birth, etc. I will create another table for the BMI, waist size, etc. data.

You are right for "tblApathy, tblDementia and tblDepression should NOT include PatientID", it's my mistake.

Now I totally understand why you are confused, it is mostly because of the field names I chose for each table.

Actually, the only table I did completely is the depression one (I will base the others on this one).

Here's a sample of what the depression questionnaire looks. It has 15 questions, and there is a total score to calculate from that.

Geriatric Depression Scale
1. Are you basically satisfied with your life? YES / NO
2. Have you dropped many of your activities and interests? YES / NO
3. Do you feel that your life is empty? YES / NO
4. Do you often get bored? YES / NO
5. Are you in good spirits most of the time? YES / NO
6. Are you afraid that something bad is going to happen to you? YES / NO
7. Do you feel happy most of the time? YES / NO
8. Do you often feel helpless? YES / NO
9. Do you prefer to stay at home, rather than going out and doing new things? YES / NO
10. Do you feel you have more problems with memory than most? YES / NO
11. Do you think it is wonderful to be alive now? YES / NO
12. Do you feel pretty worthless the way you are now? YES / NO
13. Do you feel full of energy? YES / NO
14. Do you feel that your situation is hopeless? YES / NO
15. Do you think that most people are better off than you are? YES / NO

So in my screenshot, my tbl_Depression has one field per question (I need to do some question by question analyses sometimes). Question 1 is PAT_GDS-15_1, question 2 is PAT_GDS-15_2, question 3 is PAT_GDS-15_3 and so forth..

In the apathy table, you can see fields labeled PAT_APATHY_1, PAT_APATHY_2 and PAT_APATHY_3. I think this is not a normalization problem, because in fact each of these correspond to question 1, question 2 and question 3 of the questionnaire, respectively. In this particular questionnaire, there is 14 questions so it'll go up to Pat_APATHY_14.

I hope this gives you more insight into what I'm trying to do. Sorry for before and pardon my english.

Max

 

[jdraw]

Thanks for clarifying. So a test is really recording the responses after asking a given Patient the questions in each of the 3 areas?

What actually gets repeated in a reevaluation?

Sticking with Depression for the moment. The 15 questions you posted are always given to a Patient? So, since you know the question by tblDepression Question Number, you could record PatientDepressionResponses in a similar manner

PatientId
TestTypeDepression
TestDate
DepressionResponse1
...
DepressionResponse15

I see how it isn't a Normalization issue since there are, in effect, 15 fields (responses in a DepressionTest record.

Where are you located?

 

[nightale]

Hi,

Yes the whole depression questionnaire is ALWAYS administered at full. (all the 15 questions)

PS: there will be a total of 16 tests/questionnaires, although I showed only 3 in my screenshot.

Actually a test is a specific terminology for a task, e.g. a memory test. It has many scores associated with it, e.g. free recall score, cued recall score, recognition score, learning score, etc.

A questionnaire is a specific term for something such as the depression questionnaire I posted in my previous reply. From your point of view, you can conceive it the exact same way as you would conceive a test, i.e. a patient answers to many questions and it provides a score (sometimes a yes/no answer, sometimes a likert scale response from 1 to 6, for example).

So basically for your understanding, test = roughly a questionnaire.

At the baseline level, I administer ALL the 16 tests/questionnaires (can be synonyms for you) to one patient at once. I want this instance to be called T0. (this is what I might have wrongly referred to as evaluation, pardon my english)

6 months later, I re-administer all the 16 tests to some of the patients. I want this instance to be called T1.

12 months later, I re-administer only a selection of all the tests. I want this 3rd evaluation to be called T2...

And so forth...

I am located in Quebec Canada, but I'll move to Charleston, SC for 6 months soon

Max

PS2 : you can consider that each and every of the 16 tests/questionnaires will be a lot like the depression one.

 

[jdraw]

Again thanks for clarifying. I'm in Ottawa--will head south in Jan.

there will be a total of 16 tests/questionnaires, although I showed only 3 in my screenshot.

so Apathy, Dementia and Depression are the 3, and you have 13 others.

As I understand, you will have Tx records per Patient per TestArea. In your T0, T1, T2 example, 3 records per Patient per TestArea. Each record for Depression will have 15 testResponse fields as well PK and FK for linkage and DateOfTest. Totals are done via queries or reports and do not belong in tables.

For each evaluation, it will be the very same questions given to each Patient. I'm asking just to make sure all info for review and analysis will be available in your database structure.

One thing I would suggest is that you mock up a Patient or two (or 10); use your authoritative questions and mock up some responses; and a mocked up reevaluation.

Prove to your self that the info you need for analysis can be extracted (easily). You can do most of that with pencil and paper -- after you've tested your approach and have made any obvious adjustments, then move to queries and reports for answering questions/ doing analysis.
Is there anything specific at the moment that is still an issue?

 

[nightale]

Hi jdraw,

Thanks for your time again. I'd buy you coffee if I could

All your affirmations are correct regarding my database design.

Please see my attached screenshot entitled "nightale_access.jpg". It is a mock-up patient data sample, with (of course) less questions and questionnaires than it'll be in real life. I formatted it in a non-relational way to make things simpler to visualize (so there is redundant info that I chose not to type in and left blank).

The second screenshot, entitled "nightale_mainswitchboard" is what I'm aiming at with regards to the main user interface.

The third screenshot, entitled "nightale_formnavigationtabs" is how I want the forms to be organized once you open a specific evaluation (here it's eval#2 of patient 004). I copied the layout from another database used in our lab (although no one can use it now because it is completely broken).

So Am I off to a good start the way I designed my tables so far? I'll keep all your very useful answers in mind.

I am starting to doubt my relationship logic...I am a bit confused when wording my relationships... in truth, do I have a simple one to many relationship? Just to be clear, here are my rules for my PatientQuestionnaire relationship (the same apply for each questionnaire, no matter if it is the depression, apathy or dementia one).

1) One patient can have many evaluation visits (T0,T1...TX)
2) One evaluation visit can only have every 16 questionnaires filled once.
In that case, should I do one large junction table PatientVisit instead of a junction table for each test/questionnaire?

Also...
1) One patient can be administered many times the depression questionnaire (e.g. at T0, T1, T2, T(n+1)).

2) Each Depression_ID in the tbl_Depression correspond to one physical copy of a questionnaire that was filled. Since there would be only one physical copy of that filled questionnaire in real life for one instance, this mean it can belong to only one patient at a time. (so is that really a one to many relationship, since one particular sheet of paper that has a questionnaire on it can only be placed in one patient file, although the patient can have more than one depression questionnaire in his file?) If so, am I better off without junction table and by adding the "timestamp" field to the specific patient?

(see 4th screenshot, access_V2 for a redesign of my relationships)

Sorry for all my confusion. I feel like the more I read, the worst I get...

Thank you so much!

PS: all pictures are in better resolution in a zip.

Max

 

[jdraw]

You have done considerable work. Good stuff.

A few more questions:
-It appears you are using both Access and Excel (How are your excel skills?)
(My Excel skills are minimum at best)
-Make sure of the boundaries of each(what part is in access, what in Excel)
-Are you personally giving each and every test?
-Is each and every test being done as a "paper form/questionnaire"?
-Everything will be transcribed from Paper form to Access?
-Will you be verifying the data entry?
-How is data getting into Excel?
-Will Access be used in your analysis? If so, in what capacity?

For Access, DO NOT USE special characters or embedded spaces in your field and/or object names.
Use only alphanumeric and "_" (underscore). Special characters and embedded spaces require additional syntax which is a real pain.

In my view I see tblApathy, tblDementia and tblDepression as simple reference (traditional lookup) tables.
They hold the questions for a specific test. If any question changes, then this would be anew test with a new ID. If you change questions, then your evaluations for a specific Patient would not be "similar/exact replicates" for comparison.

When you evaluate/test Patient 15 with DepressionTest 001 you would have a junction table such as tblPatientDepressionEvaluation with fields

PatientDepressionEvalID PK
PatientID FK (to PatientTable) Patient 15
DepressionTestID(FK) (to tblDepression) say 001 for example
PatientDepressionEvalDate (Date of this Evaluation) July 15 2013
R1
R2
..
RX

Where R1--Rx are that Patients responses to Q1--Qx in DepTest 001

In this table I would make a unique composite index of
PatientID
DepressionTestID
PatientDepressionEvalDate
This would be done to ensure no duplicates are possible.

The next time you do the Depression Evaluation with this Patient, a new record would be created with same PatientId, same DepressionTestID and a new PatientDepressionEvalDate Jan 14 2014 - The R1--Rx are specific to this new Evaluation.

Other set ups are possible I'm sure, but does this make sense or align with your approach?

 

[nightale]

Hi,

Once again thanks for your time.

-It appears you are using both Access and Excel (How are your excel skills?)
(My Excel skills are minimum at best) : actually, I only used Excel to show what data will be recorded (Patient x Evaluation x Test). I am OK with Excel, but I find it pretty limited with regards to holding a large database (I'll have a total of over 1400 fields across 16 tests and questionnaires) and filtering/sorting the data.

-Make sure of the boundaries of each(what part is in access, what in Excel) Actually, I want everything to be in Access.

-Are you personally giving each and every test? For half of the patients, I will personally give each and every test. I have colleagues that will help me evaluate some patients using the same exact protocol.

-Is each and every test being done as a "paper form/questionnaire"? Yes. Each and every test is associated with a paper form/questionnaire. During the evaluation, the examiner writes down the examinee responses directly in a printed document.

-Everything will be transcribed from Paper form to Access? Only what need to be analyzed in SPSS/SAS/MathLab softwares.

-Will you be verifying the data entry? I have two assistants and I plan on making them crossvalidating the data entry of each other. I also plan to use Access to limit what they can input in each field. For instance, if the field "Memory score" can be from 1-10, they cannot enter 15.

-How is data getting into Excel? It won't, I only used it to show you what It can look like.

-Will Access be used in your analysis? If so, in what capacity? Yes. I want access to be able to extract a spreadsheet with all the variables (fields) I selected and according to all the criteria and filters I've applied. I'll be the only one to extract data from it. So I might just build custom queries on the go. For instance, I might want the all the data for T0 for all patients over 50 years old, with a score over 5 on the depression questionnaire

In my view I see tblApathy, tblDementia and tblDepression as simple reference (traditional lookup) tables.
They hold the questions for a specific test. If any question changes, then this would be anew test with a new ID. If you change questions, then your evaluations for a specific Patient would not be "similar/exact replicates" for comparison.

Oh! I see. That way I could change one or more questions and only have to modify one place.




Regarding relationships... I was wondering if from my large junction table (tbl_PatientVisit) to all my tests (tbl_Depression,tbl_Apathy,tbl_Dementia) if it should rather be a one to one relationship.

From tbl_Patient to tbl_PatientVisit, it is obviously a one-to-many relationship, because "one patient can have many visits; many visits can have one patient".

From my junction table "tbl_PatientVisit" to my tests, shouldn't it be a one-to-one relationship instead of a one to many? Because "one patient's visit can have only one instance of each test; one instance of each test can only have one patient visit (and one patient) associated with it".

Am I seeing this the wrong way? Because that way, it seems to me that a one-to-one relationship is much more relevant. In that case, the only reason to separate all the tests entities and their scores attributes from each other is because there is a quite large number of fields per table; this is what I did.

How I see it :

tbl_Patient
Patient_ID --- pk = one patient file

tbl_PatientVisit (junction table)
PatientVisit_ID ---- pk = one patient visit that is embedded in one patient file
Depression_ID ---- fk = one paper questionnaire of depression, associated with that Patientvisit and that patient.
Apathy_ID...
and so on.

By saying "Depression_ID ---- fk = one paper questionnaire of depression, associated " is this where I got it wrong?

 

[jdraw]

-Everything will be transcribed from Paper form to Access? Only what need to be analyzed in SPSS/SAS/MathLab softwares.

Do you foresee something not being stored in Access given your comment

Actually, I want everything to be in Access.

I'm not sure we are seeing the same tables.
I see your testAreas (Apathy, Dementia, Depression) as separate "things". From my understanding of your "business", I would have these as separate Lookup tables. In each TestArea would have a unique ID. The fields in these tables would represent the Questions for that TestArea. Ideally these records would be read only since they would not change from
a) one patient to another
b) one evaluation to another.

The junction table(s) I see are the
Patient_TestArea_Evaluation which would be set up for each TestArea (Apathy, Dementia, Depression...)

Patient(1)-----> PatientDepressionEval(M)  Records would be T0, T1, T2... responses
    |
    +----------->PatientApathyEval(M)       Records would be T0, T1, T2... responses
    |
    +------------>PatientDementiaEval(M)  Records would be T0, T1, T2... responses
    |
    + ----->same set up for your other TestAreas

The records in these tables would be the responses by Patient by DepressionQuestion by Evaluation.

I would not make 1 large table that had PatientAllTestAreasAllEvals. 
It could be done but allTestAreas would be in one table and be difficult to query.

That is how I see things at the moment, but haven't mocked anything up. So changes are certainly possible.

 

[nightale]

Hi,

Oh! This is very eye-opening for me!

Quote:
-Everything will be transcribed from Paper form to Access? Only what need to be analyzed in SPSS/SAS/MathLab softwares.
Do you foresee something not being stored in Access given your comment
Quote:
Actually, I want everything to be in Access.

Regarding the quote above, I wasn't clear. I want everything I need for analyses in Access, the rest will stay on the paper file of each patient, because I'll never use them for group analysis and I'll never need them. (for example notes about the patient caregiver, etc.)

Patient(1)-----> PatientDepressionEval(M)  Records would be T0, T1, T2... responses
    |
    +----------->PatientApathyEval(M)       Records would be T0, T1, T2... responses
    |
    +------------>PatientDementiaEval(M)  Records would be T0, T1, T2... responses
    |
    + ----->same set up for your other TestAreas


Patient(2)-----> PatientDepressionEval(M)  Records would be T0, T1, T2... responses
    |
    +----------->PatientApathyEval(M)       Records would be T0, T1, T2... responses
    |
    + ----->same set up for your other TestAreas

... same structure for every patient.

Your "code" example really helped me understand it from a different perspective and it makes a lot of sense. The way I first saw it was like this :

Patient (001) --->  Eval (T0)  ---> DepressionResponses_ID
                                         ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID
                    ---> Eval (T1)  ---> DepressionResponses_ID
                                         ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID
                                        ....
                   ---> Eval (Tx)   ---> DepressionResponses_ID
                                          ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID

Patient (002) --->  Eval (T0)  ---> DepressionResponses_ID
                                         ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID
                   ---> Eval (T1)  ---> DepressionResponses_ID
                                         ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID
                         ....
                   ---> Eval (Tx)   ---> DepressionResponses_ID
                                         ---> ApathyResponses_ID
                                         ---> Dementia_responses_ID

But now, I see it might be more efficient to put it as you suggested, with composite primary keys, i.e. =

Patient (001) --->   PatientDepressionEval  --->  Records = T0 set of responsse, T1 set of responses, etc.
                   --->   PatientApathyEval      ---> Records = T0 set of responsse, T1 set of responses, etc.
                   --->   PatientDementiaEval   --->Records = T0 set of responsse, T1 set of responses, etc.

Patient (002) --->   PatientDepressionEval  ---> Records = T0 set of responsse, T1 set of responses, etc.
                   --->   PatientApathyEval      --->Records = T0 set of responsse, T1 set of responses, etc.
                   --->   PatientDementiaEval   --->Records = T0 set of responsse, T1 set of responses, etc.

...

That way, I think it'll be easier to extract data for each patient. I can see an analogy where Patient = customer; Eval = Order; Tests = products.

Is there a way to program a field in visual basic so that it automatically creates a label for each record in each table? (label t0,t1,t2) (just like autonumber automatically adds a +1 to the previous records). Can a lookup table contaning all my T(n+1) labels help me?

There is still one thing I don't understand : what's the point of having a table where all the DepressionQuestion# for one questionnaire are listed as read-only fields and another where it is DepressionResponse#? I guess this is useful if I change or add one question to the questionnaire, but is there any other use?

Wow, once again many thanks for your help. I think with these last questions I'll be off a good start and I'll repost later when I'll be more advanced with my database design.

 

[jdraw]

Max,

I'm just trying to put a structure together that matches what you're saying. I haven't done this question/response thing fo a long time (if ever).

Yes re the model of Customer, Order, OrderDetails and Product. It's a model many have dealt with.

Re:Is there a way to program a field in visual basic so that it automatically creates a label for each record in each table? (label t0,t1,t2) (just like autonumber automatically adds a +1 to the previous records). Can a lookup table contaning all my T(n+1) labels help me?

Autonumbers may appear to be incremental, sequential numbers but that is NOT TRUE absolutely. Autonumbers by definition are guaranteed to be Unique -- that's all! They may not be sequential and they may not be positive.

What exactly do you want with an automatic label?

RE:There is still one thing I don't understand : what's the point of having a table where all the DepressionQuestion# for one questionnaire are listed as read-only fields and another where it is DepressionResponse#? I guess this is useful if I change or add one question to the questionnaire, but is there any other use?

Suppose you had a list of 100 (or 1000) questions related to Geriatric Depression. Now suppose you were going to make up a questionnaire for testing Patients and you had time for only 25 questions. This would allow you to select a group of 25 from your authoritative list of 100. If you tagged those 25 with your code eg Max1_Sept2013; you or anyone else could select another 25 at random from the list and tag them eg JoeH2012. These would represent 2 separate tests of 25 questions each. You could reference the question number from the authoritative list. Any responses to the same questions based on the authoritative question number would be comparable - even though the tests themselves could have been created independently.

The bottom line of this is if you change some of your questions during the evaluation period, as long as you keep the original numbers for cross reference, you could still do comparative analysis on those questions that are equivalent.

I have created an initial data model(attached) to show a structure. I haven't mocked up tables in Access with data to see if it meet your needs.

We've certainly discussed and clarified items that may help you with your project.
Perhaps others will comment to confirm or adjust to make things fit your needs.

 

[nightale]

Thank you so much for your help.

I understand what you meant with that screenshot of yours. However, I've reconsider this aspect : all my tests/questionnaires are standardized and will not change over time. The only thing that may happen is that a test/questionnaire will be added to the battery, but that's it. Since all test/questionnaires are standardized, they will never change over time. They are commercial tools with norms and validation data, so they are quite solid.


I've attached (nightale_otherway.png) something I found while browsing the net... They were designing an Access database and they used that approach, which is close to what I want to do (indefinite number of visits by patients).

What do you think of that? Is this coherent with what I'm trying to create in my new approach for table relationships? (see nightale_relationships.png)

My rules are :
1) One patient can have many visits. Many visits can be related one patient.
2) One patient visit can only have one record for each questionnaire (hence the one to one relationship). One record for each questionnaire can only belong to one patient visit.

So now, one-to-one relationships means all my questionnaire tables could be merged in one, but I don't want to do that given the large number of fields for some of them (over 200+ for one questionnaire).

Thanks!!!

 

[jdraw]

Do you have a Patient Visit where you do not have an evaluation/questionnaire completed?

Did you review and understand the data model I attached to my post yesterday? It allows for multiple Visits (re-evaluations) by Patient for each questionnaire and records responses to questions.

Re: Your nightale_relationships:
The fields in tblPatient seem to be 1 to 1 with tblPatient_ClinDemo and do not seem to be related to tbl_PatientVisit. So there is no need for the realtionship to tbl_PatientVisit.

Also, I realize you are relatively new to forums and that your posts are not truly duplicated, but when you post in multiple threads with similar or ongoing questions on same/similar topic, it is considered "good manners/etiquette" to advise the readers of the other post(s) and even include a link(s).

I realize this is not a duplicate, but thought I'd comment
http://www.accessforums.net/access/...tionship-patient-can-have-multiple-39666.html

Here's the rationale

 

[nightale]

Hey there,

Yes. It is possible that, for instance, at visit 2, patient 1 completes only the depression and apathy questionnaires, but not the dementia one.

I reviewed your model. If we let go the lookup table for the questions, we have now only the Patient and PatientTestDepEval and the TestDep tables.

So "PatientTestDepEval" is an entity that represent the record for one Patient X for a TestDep_ID X at Eval X. It seems a possible alternative, but correct me if I'm wrong. The problem I see with that approach, is that one PatientVisit (visit and eval are synonyms to me) can have multiple records of the same test, right? I need to limit it so that one visit = only one instance of the depression questionnaire.

Also, I would need a junction table "PatientTestXXXEval" for each test/questionnaires, which seems cumbersome (so again, why not only one large Patient_ALLTESTS_Eval table with PatientEval_ID (pk), Patient_ID (fK), TestDepression_ID (fk), TestApathy_ID(fk), TestDementia_ID(fk).... PatientEvalDate.)

Thanks!

 

[jdraw]

Yes. It is possible that, for instance, at visit 2, patient 1 completes only the depression and apathy questionnaires, but not the dementia one.

I think we didn't see the same thing here. My question was really -- Do you ever have a PatientVisit and no questionnaires is completed? The reason is that each record in table
PatientTestDepEval is a record of that Patient, that completion of the Dep questionnaire, reference to each question and a record of each Response and that EvaluationDate.
This table would not satisfy a PatientVisit where no Evaluation was done (no questionnaire completed). It would handle the case of say ApathyEvaluation and Dementia, but no DepressionEvaluation on that Visit.

I'm going to revise the model I have to show all your TestTypes(Depression, Apathy, Dementia, x, x1,x2,x3.... x 99) and each with questions (various number of questions for each questionnaire) since your questionnaires are fixed throughout (no question changes/substitutions).
I don't think all of these are needed

...one large Patient_ALLTESTS_Eval table with PatientEval_ID (pk), Patient_ID (fK), TestDepression_ID (fk), TestApathy_ID(fk), TestDementia_ID(fk).... PatientEvalDate.)