Table organization

[huv123]

Hi I have created a database that is meant to collect data about a study I am doing. It contains 2 main tables - 1 table regarding the staff that is working on the study and 2. Details about the participants and all their data (derived from a number of different forms/questionnaire).

Initially I had each questionnaire on a separate table but someone said it was not good database design and since there is a 1:1 relationship between the details about the participant and each of the questionaires, i.e. each participant cannot only fill out each questionnaire once) that I should have it all in one table.

However TheAceMan1 from the Forms forum said I should come here to discuss the table design etc since I am having issues with the design of one of my forms.

I am not certain about why he thinks there is an issue or why if there is an issue why its causing my problems but you can view the discussion here:

http://www.tek-tips.com/viewthread.cfm?qid=1443127&page=1

Here is a copy of the database also. The data is dummy/fake.


If anyone could help me as soon as possible I would really appreciate it. I am supposed to start using the database soon.

 

[huv123]

This is from the TheAceMan1

The problem is you have a table with:
Q1L1
Q1L2
Q1R1
Q1R2
Q2L1
Q2L2
Q2R1
Q2R2
'
'
'
Q?L1
Q?L2
Q?R1
Q?R2

or dedicated checkboxes for each question! Not a good way to go espcially since each question has the same number of checkboxes.

Your getting the error because the code is checking (or will be) all questions at one time, instead of the current question record! This changes the code to where you have to detect what question line/record your on!

 

[lespaul]

I can't decide from this:

each participant cannot only fill out each questionnaire once)

if each participant has ONE questionnaire or MANY questionnaires...for my example below I'm going to show you how it's set up when the participant has ONE questionnaire based on your previous statement that it's a 1:1. I also can't be sure if you have a single questionnaire or multiple.

Most question/answer ("survey") databases are set up more like:

Participants
ID (PK)
Name
Address
ContactNumber
{anything else having to do with the participant)

Questions
QuestionID (PK)
QuestionText

Survey
ID (PK)
ParticipantID (FK)
DateTaken
{any other information about the questionnaire. If you have multiple questionnaires you would add a FK to another table that lists the different types of questionnaires}

ParticipantAnswers
ID (PK)
QuestionID (FK)
Answer
{any other information about the Answer you need. If a person could have many surveys, then you would need to add the surveyID field to this table in order to determine which answers belong to which survey. The answer field can be set up many ways depending on the type of information you are collecting: text, specific choices, etc.}

Has anyone directed you to The fundamentals of relational database design?

You may also want to heck out "At Your Survey" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[fneily]

Just saw LesPaul posted before me, but after all the typing I did, I'll post mine anyway.

First about psychologists, they're not Access programmers. I did some statistical work for B.F. Skinner in his last year at Harvard. They just need the data. Your form can look the way they want - that doesn't mean you have to lower yourself down to their level. How that form works is up to you - you are the programmer remember that.

The AceMan1 made a point that you never answered - why not just have the users select from the table TheAceMan1 suggested:
Strong Left (both boxes checked)
Strong Right
Medium Left (only one check)
Medium Right
Dont Care (checks in both)
This could be a dropdown box next to each question.

Then you PROGRAM the check marks on the form. And because the type reflects the checkmarks, you don't have to store the checkmarks, just the type for that question. There's no reason the user has to ACTUALLY have to do the checking themselves. Don't think like a psychologist but as a programmer. The collected data is the same.

Then you would have just four tables: tblClient, tblType, tblQuestion, tblClientAns.
tblClient has client info.
tblType has the five selections as above with either an autonumber primary key or make up one like Ans1, Ans2, etc.
tblQuestion has a primary key reflecting the question such as Writing would be Wr, and a description field.

tblClientAns would be a so called Junction Table. You have a many-to-many relationship, a user can have many answers and an answer can belong to many users. So this table would look like:
ClientAnsID ClientID TypeID QuestionID Date Etc
ClientID TypeID QuestionID are just regular fields in the junction table. Date Etc. are any othe COMMON fields that you want tracked.

Now using queries and a little coding, you can do any kind of analysis you want. And since psychologists aren't statisticians, you can create all kinds of graphs that they won't understand.

Also, you may want to see a suggestion from Remou:

Have a look at "At Your Survey" available at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[huv123]

if each participant has ONE questionnaire or MANY questionnaires...for my example below I'm going to show you how it's set up when the participant has ONE questionnaire based on your previous statement that it's a 1:1. I also can't be sure if you have a single questionnaire or multiple.

I emant to s say that there are 6 questionnaires that each participant can only do each questionnaire once.

[qoute] The AceMan1 made a point that you never answered - why not just have the users select from the table TheAceMan1 suggested:
Strong Left (both boxes checked)
Strong Right
Medium Left (only one check)
Medium Right
Dont Care (checks in both)
This could be a dropdown box next to each question.
[/quote]

Because this would br changing the design of the questionnaire. I am not a psychologist either (despite the qustionnaire) but a physiologist (probably just as bad) and they are uts about their questionnaires. You cannot add or sutract from the instructions, you cannot change the physical apearance of the form.

 

[lespaul]

The table design is not driven by the form or what it looks like...it is driven by the data you are storing.

The presentation of the data in the form can be however the users wants it, the design of the table should be set up according to the rules of database design (the fundamentals link above takes you to a document that describes the "rules" of tables and relational databases).

So you have six questionnaires...you are going to want to add a questionnaireid field to the Survey table above to indicate WHICH questionnaire they took that day.

Leslie

 

[huv123]

Les Paul Can you go through your answer with me? I am not sure I understand

 

[fneily]

Because this would br changing the design of the questionnaire."
No. It would not.

Each participant has 6 questionaires. A questionaire can be done by many participants. Many-to-many relationship. You can add a questionaireID to my junction table.

You seem to be locked in a certain mind set.

Read Lespaul's comment 100 times. Table Design and Structure are done first, correctly and is the most important. Forms can be anything you want them to be, but they're later in the process. Tables first.

 

[lespaul]

So let's say I am a participant and I have taken two of the questionnaires.

Here's my participant record. I have an ID of 100
Participant
[tt]ID Name Other Fields
100 LesPaul .....[/tt]

Here are all the questions that are possible on ANY questionnaire
Questions[tt]
ID QuestionText
52 Writing
53 Using a spoon[/tt]

I just added this after taking a better look at your other post
Each question has the same answers and so I list them all here:
Answers[tt]
ID AnswerText
1 Only use Left hand
2 Only use Right hand
3 could use either hand[/tt]

This table allows you to identify the different questionnaires:
Questionnaires[tt]
ID Name
1 Questionnaire1
2 Questionnaire2
3 Questionnaire3
4 Questionnaire4[/tt]

here are the two surveys I took. Survey#1 was taken on 1/15/2008 and #2 on 2/16/2008:
Surveys[tt]
ID ParticipantID SurveyID DateTaken
1000 100 1 1/15/2008
...
1500 100 2 2/16/2008[/tt]

Here are the answers to ALL the questions that ANYONE taking the survey has given. When I took the survey#1 (which corresponds to SurveyID 1000) I answered the two questions and answered I would only use my right hand (answerid #2 above). When I took survey#2 (which corresponds to SurveyID 1500) I changed my answer to question52 but have the same answer for question53.

ParticipantAnswers[tt]
ID SurveyID QuestionID AnswerID
2002 1000 52 2
2003 1000 53 2
....
3015 1500 52 3
3016 1500 53 2[/tt]

This is the way your tables and data should be structured according to the rules of database design. There is no duplicate information, there are primary and foreign keys that relate all the information together. With a structure like this you will be able to do most counting queries very easily to get the data your user will need.

Leslie

 

[huv123]

Fneily - I meant that cannot use combo boxes for selection of answers.

I have been criticised for not thinking like a programmer - sometimes a programmer will need view the problem through the eyes of a psychologist.

 

[lespaul]

except at this point we are talking about storing data....most programmers don't do this correctly either...

get the tables right and everything else can be worked out.

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[huv123]

Hi Leslie - but the participant will only ever take Questionnaire 1 once, Questionnaire 2 once, and so on.. doesnt that make the survey table redundant?

Also I am trying to link the form/control source to a table which one am I doing it to?

 

[huv123]

Also there are 15 possible combinations of the 4 checkboxes per question - most of which are errors and which I need to trigger a msg bo - how does this relate back to the table answers?

 

[lespaul]

doesn't matter...you have many questionnaires (six of them) and many participants (hundreds?thousands of them?), that's a many to many relationship.

You have to be able to identify which questionnaire the person took on which day. Now if you have a single questionnaire that a person takes 6 times that's a one to many relationship..you have 1 questionnaire that the person takes many times.

what does the form/control source need to show? I would expect a form/subform set up where the participant information is on the top, the questionnaires are on the bottom...you'll have to talk with someone else about the forms and stuff...i don't really program in Access, I'm just a table and query helper.

Leslie

 

[huv123]

That isnt how I have structured the forms. You can take alook - I uploaded it in my first post. I am trying to see how I cant this structure in my existing database without starting again.

 

[huv123]

cant strcture = can structure

 

[lespaul]

Like I said, I'm not an Access programmer and don't have it installed so I can't look at your database...Is this the form that the person taking the questionnaire enters answers into (or some user (you?) taking a paper copy and entering into the database?)

As I also stated above, if you don't get the table design right at the beginning all the other development gets really icky.

In order to help you with your form development, you would need to tell us about the tables you DO have (since you appear to not be implementing the correct table structure).

So, (not really) pretending that I don't know anything about this situation...if the question is:

Eating with a spoon

how are there 15 different combinations of answers for this question? From your other post I see:
1. Always use RIGHT hand
2. Always use LEFT hand
3. Could use either hand
4. Don't care which hand
5. Don't perform task

then there are apparently "scores" that are tied to these answers...so where are the 15 choices?



Leslie

 

[fneily]

view the problem through the eyes of a psychologist." No thank you. The psychologist want to collect data. They want the form to look a certain way. So be it.
Point 1: How you STORE the data, they have no concept of, nor should they care. They obviously never heard of Relational Database's NORMALIZATION and they probably don't want to learn it. This has to do with the structure of your storage areas called Tables. If they are not constructed correctly, then you run into problems such as the one you're having now.
Point 2: The form would show your checkboxes, but instead of actually placing a check in the boxes, there'd be a combobox with the 5 selections. After the selection is made the appropriate checks would appear. This is "user friendly" and simple to store. It is collecting EXACTLY what the psycho's want.
I'm sure you'll do what you want, but you could be finished by now.

 

[huv123]

haha I would be finished 6 weeks ago if I could have done what I want. Instead I have to work within the constraints that I have been given.

i have heard of normalization and relational databases, only the entire instruction took place over 3 hours 3 years ago ina data managment course . Yes I have a lot to learn but I will get there eventually.

Bet you couldnt pull a clinically trial out of your pocket

 

[huv123]

Okay to describe my database:

Right now I have three tables -

1 for passwords, that isnt linked to anything else

2. one that holds all the info about the participant including who they were interviewed by, gender, height, weight etc as well as all the data for the questionnaires. Before I had all the data in a seperate table linked by ParticipantID PK & FK) in a one to one relationship until someone said that wasnt good management.
2. Information about research personnel. I have a form that inputs this data but it actually appears in the "Interviewed By" field in the patient details. Its not linked to anything but probably should. I think its a one-to-many relationship(?) - each personnel can have many participants but each participant can only have one personnel member assigned to them.

I also have 6(+ other forms for filtering)forms - each representing one questionnaire which is a computer version of a paper questionnaire. Except for this handedness questionnaire I do not think most of the answer options in the different questionnaire are duplicated within the questionnaire or between questionnaires except in a few cases. All the data is dumped in the participant details table.

The handedness questionnaire is awkard because there is no such thing as a group of "checkboxes". However in this questionnaire there are ten questions - each of which ask about a particular task and are given 4 checkboxes -2 in a "Left Column" and 2 in a "Right Column". If a participant does the task with a specific preference for a hand such as writing with the left hand they must put 2 ticks in left column. If they dont care which hand, they place one tick in each column and if they never do the task they dont put anyticks in either column. There must always be two ticks (or no ticks) for each question.

To get a final score, all the ticks are counted in the left column and all the ticks are counted in the right column.

The number of ticks in the left column are minused from the number of ticks in the right column and divided by the total number of ticks in the two column. This is then X 100. THe range can be between -100 and +100 - the score determines if a person is left handed, right handed or ambidextrous.

With each question containing 4 boces there are fifteen possible combinations - there are only 7 possible "correct/valid" responses and 8 error responses as follows:

L1 L2 R1 R2
Dec Bin Bin Bin Bin
Num 8 4 2 1 Condition
*** *** *** *** *** ************
00 0 0 0 0 No Preference
01 0 0 0 1 Error
02 0 0 1 0 Error
03 0 0 1 1 Strong Right
04 0 1 0 0 Error
05 0 1 0 1 No Preference
06 0 1 1 0 No Preference
07 0 1 1 1 Error
08 1 0 0 0 Error
09 1 0 0 1 No Preference
10 1 0 1 0 No Preference
11 1 0 1 1 Error
12 1 1 0 0 Strong Left
13 1 1 0 1 Error
14 1 1 1 0 Error
15 1 1 1 1 Error


I am trying to create a validation process to make sure that people answer the questionnaire properly.

Right now each checkbox fills a field and then totals etc are calculated with queries.

One reason that each box has a field is that I need to be able to open the form at a later date to see visually how the people answered. If it is not bound there information is lost once I close the form. I cannot use combo boxes to choose the answers.