Survey Design

[men]

Apologies, I am asking for quite a bit of help. I have been working on
this in spurts for about 1 month. I feel like if I ever could just
devote a several hour long session to this that I could figure it out
myself. But, as many, I just can't seem to find a long stretch of
uninterrupted time like this.

Now I am 1 week away from the deadline of needing to get this done and
I am hoping that somebody in this group would be knowledgable, have
time and be nice enough to help me with this.

I have looked and struggled through comprehending AYS, it has taught
me about proper field use (normalization) and several other aspects of
design but I need to do this one from scratch based on duplicating a
paperorm for proper data entry by untrained people.

I am trying to design a survey where a patient (client) fiils out a
questionaire every few months which tracks his mood changes. There are
5 categories made up of a total of 22 questions and each question can
only have 1 out of 5 possible answers and each answer has a preset
value. for example:

Question 1)
Do you feel Discouraged or hopeless?

Possible answers are:

0-Not At All (value = 0)
1-Somewhat (value=1)
2-Moderately (value=2)
3-A Lot (value = 3)
4-Extremely (value=4)

So far I have the following tables:

tblClients w. ClientID as primary key
(fields are FirstName, LastName, DateofTest, QuestionID, AnswerID)

tblQuestions w. QuestionID as primary key
(fields are Question, AnswerID, ClientID)

tblAnswers w. AnswerID as primary key
(fields are Answer, QuestionID, ClientID)

I have a lookup table for the 22 questions tied to the "Question"
field in the tblQuestions and a lookup table for the 5 answers tied to
the "Answer" field in tblAnswer.

First, is this a good design?

Second - Are my relationships correct?
"tblClient" one to many "tblQuestions"
"tblClient" one to many "tblAnswers"
"tblQuestions" one to many "tblAnswers"

Third - I am not sure what to do with referrential integrety, enforce
update and delettions and join type. I guess I just don't understand
this part well enough, yet to know what settings matter and which way
they should be set.

Fourth - I have reproduced the paper form for input. There are the 22
questions (as labels) running one under another staright down the left
column of the page. In line with each question is an option box with 5
mutually exclusive radio buttons (which makes a column of 22 option
boxes exactly (size and layout-wise) the same, On top of the option
box column are vertically arranged labels with each label for each
question.
I can't figure out how to logically tie this form to the tables so
that it works as a data entry tool. I get bits and pieces but just
have not been able to clearly understand how to tie all this together.

Fifth - I need to have a calculated total based on the answers and
track each clients pro/regress over the time they have participated in
the survey.

The final result would be something like: Client X has taken the
survey Y number of times on the following dates and in the Category of
"Depression" his scores have been as follows and in the Category
"Anxiety" his scores have been as follows. Therefore client X has
shown improvements in his Depression and his Anxiety has increased or
decreased. So these 22 questions represent 5 such categories and I
have to report on pro/regress for each client on each category.

Thanks for staying with me. Hopefully you have enough time to help me
get this finished.

 

[Amy Blankenship]

Apologies, I am asking for quite a bit of help. I have been working on
this in spurts for about 1 month. I feel like if I ever could just
devote a several hour long session to this that I could figure it out
myself. But, as many, I just can't seem to find a long stretch of
uninterrupted time like this.

Now I am 1 week away from the deadline of needing to get this done and
I am hoping that somebody in this group would be knowledgable, have
time and be nice enough to help me with this.

I have looked and struggled through comprehending AYS, it has taught
me about proper field use (normalization) and several other aspects of
design but I need to do this one from scratch based on duplicating a
paperorm for proper data entry by untrained people.

I am trying to design a survey where a patient (client) fiils out a
questionaire every few months which tracks his mood changes. There are
5 categories made up of a total of 22 questions and each question can
only have 1 out of 5 possible answers and each answer has a preset
value. for example:

Question 1)
Do you feel Discouraged or hopeless?

Possible answers are:

0-Not At All (value = 0)
1-Somewhat (value=1)
2-Moderately (value=2)
3-A Lot (value = 3)
4-Extremely (value=4)

So far I have the following tables:

tblClients w. ClientID as primary key
(fields are FirstName, LastName, DateofTest, QuestionID, AnswerID)

tblQuestions w. QuestionID as primary key
(fields are Question, AnswerID, ClientID)

tblAnswers w. AnswerID as primary key
(fields are Answer, QuestionID, ClientID)

I have a lookup table for the 22 questions tied to the "Question"
field in the tblQuestions and a lookup table for the 5 answers tied to
the "Answer" field in tblAnswer.

First, is this a good design?

It's not clear what you're doing. If tblQuestions is NOT the questions,
then what is it doing? Your statement that you have a lookup table "tied
to" it suggests it is not. If it does contain the questions, then what is
the table doing?

Second - Are my relationships correct?
"tblClient" one to many "tblQuestions"

That doesn't sound right. The questions are the questions. The clients are
not related to the questions.

"tblClient" one to many "tblAnswers"

This may or may not be right. If tblAnswers are the possible answers (0-4),
then this is wrong. Those answers are what they are, and not related to the
clients either. If, instead, tblAnswers is the answers a particular client
gave to a particular question, then it would be a junction table between
Client and Question. However, the design you have only will work if you
only expect to capture one answer to each question, not track the answers
over time. To track over time, at the least you need to add a date/time
stamp, but I'd suggest you add an answerSession table to group answers
together by when the Client answered it.

So, you'd have this:

tblQuestion
QuestionID
QuestionText

tblPossibleAnswer
AnswerID
AnswerText

tblClients
ClientID
(Other stuff)

tblAnswerSession
AnswerSessionID
ClientID
AnswerDate

tblAnswers
AnswerSessionID
QuestionID
AnswerID

Third - I am not sure what to do with referrential integrety, enforce
update and delettions and join type. I guess I just don't understand
this part well enough, yet to know what settings matter and which way
they should be set.

Fourth - I have reproduced the paper form for input. There are the 22
questions (as labels) running one under another staright down the left
column of the page. In line with each question is an option box with 5
mutually exclusive radio buttons (which makes a column of 22 option
boxes exactly (size and layout-wise) the same, On top of the option
box column are vertically arranged labels with each label for each
question.
I can't figure out how to logically tie this form to the tables so
that it works as a data entry tool. I get bits and pieces but just
have not been able to clearly understand how to tie all this together.

You need to have a continuous form that pulls from a Left Join of
tblQuestions and a join of tblAnswerSessions and tblAnswers.

Fifth - I need to have a calculated total based on the answers and
track each clients pro/regress over the time they have participated in
the survey.

You can do that with a query or a report.

HTH;

Amy

 

[men]

What a nice feeling to wake up, stumble to the computer and find that
somebody has already responded, THANKS, Ami!

I only had time to peruse your answer. I printed it out and will take
it with me to work where I will start reading it with comprehension as
I find time through the day. I will respond in more detail tonight as
it looks like you and I have a few more questions/answers. to
clarify.
In the meantime, you made my day!!! Thanks so much!

 

[Roger Carlson]

It is an Incredibly Bad Idea to design your database based on a form. Ease
of entry or not, you are setting your self up for huge problems with
reporting your data if you store it in a non-normalized fashion. And then
what's the use of ease of entry if you can't get the data back out to report
it?

You would not believe the horrible databases I've seen which were created by
slavishly copying the form. Here's and example:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=ReallyBadDatabase.mdb.
My suggestion is to use a normalized data model (ala AtYourSurvey) and
simply train your data entry people.

If you absolutely MUST have a data entry form that looks like the survey,
create one that enters the data into a non-normalized table which you then
transform (via code) into a normalized structure. The specifics of how you
do that will depend on the details of your database, but I have a simplified
example here that might get you started:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=NormalizeDenormalize.mdb


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[men]

Hi Ami - I just got home and went straight to the computer to input
your table design. Your layout feels elegant compared to my boxed in
thinking. I guess i have had my nose too close to the grindstone for
too long.

Hopefully you can bear with me for a fe more questions on your design:

So, you'd have this:

tblQuestion
QuestionID
QuestionText

tblPossibleAnswer
AnswerID
AnswerText

tblClients
ClientID
(Other stuff)

tblAnswerSession
AnswerSessionID
ClientID
AnswerDate

tblAnswers
AnswerSessionID
QuestionID
AnswerID

Are the following the correct Primary keys?

tblQuestion = Question ID (number/no dupes)
tblPossibleAnswers = AnswerID (number/no dupes)
tblClients = ClientID (autonumber/no dupes)
tblAnswerSession = AnswerSessionID (autonumber/no dupes)
tblAnswers = ??? not sure

What are the relationships?
The only one I think I see correctly is tblClients onetomany
tblAnswersessions. But maybe I am wrong - I just wanted to proof to
you that I am trying to work this out myself as much as possible.
Again I have not have enough time, yet, to ponder the proper
relationships and I would appreciate your wonderful help on this

You need to have a continuous form that pulls from a Left Join of
tblQuestions and a join of tblAnswerSessions and tblAnswers.

I sense that this is a complete answer for somebody who knows what
they are doing. Left join means answer number 2 on the "type of Join
dialog", no? Is a 'join" number 1?

If possible, could you tell me in more layman's terms such as here are
the relationships and the corresponding joins.

Could I then use the form wizard to make a form which has tblClient,
tblQuestions, tblAnswerSessions and tblAnswers? Pretty it up
afterwards? Or is the wizzard too limited?

You can do that with a query or a report.

Once I get the tables and form done I think i'd be OK figuring out the
correct queries/reports. In other words I am hopeful that I would not
have to take up any more of your time. I already feel guilty enough

HTH;

Your help is not only technical it's also giving me totally new hope
and enthusiasm for this project. I have already learned a lot on my
own by trying to get this done but you have really opened my eyes on
how to think about this project. I am sorry if this sounds gushy but
your help has made me realize how bummed out and frustrated I had
become. I really want to learn how to do databases well, thanks a lot
for your time and help!

 

[men]

Just when I was so proud of my ability to design a good looking form

However, I really want to thank you for taking time to starighten me
out on this issue!!

You certainly saved me a lot of time and frustration and I can assure
you that I'll NEVER forget this lesson.

Much appreciated

Thanks!

 

[Amy Blankenship]

Hi Ami - I just got home and went straight to the computer to input
your table design. Your layout feels elegant compared to my boxed in
thinking. I guess i have had my nose too close to the grindstone for
too long.

Hopefully you can bear with me for a fe more questions on your design:



Are the following the correct Primary keys?

tblQuestion = Question ID (number/no dupes)
tblPossibleAnswers = AnswerID (number/no dupes)
tblClients = ClientID (autonumber/no dupes)
tblAnswerSession = AnswerSessionID (autonumber/no dupes)
tblAnswers = ??? not sure

I guess you could say that all three are the primary key, in that you would
not expect to have more than one answer to any one question in a given
session. If you wanted a single key for some reason, you could give it
another Autonumber field. However, unless you're hand writing SQL, I'm not
sure there's much advantage to it, since it does not provide a foreign key
to any other table.

What are the relationships?
The only one I think I see correctly is tblClients onetomany
tblAnswersessions. But maybe I am wrong - I just wanted to proof to
you that I am trying to work this out myself as much as possible.
Again I have not have enough time, yet, to ponder the proper
relationships and I would appreciate your wonderful help on this

You are right, there is a one to many relationship between tblClients and
tblAnswerSessions. Each client will have many sessions in which they
answer. tblAnswerSession then has a one to many relationship with
tblAnswers (the user will answer many questions within a session). This
obviously implies a one to many relationship between tblAnswers and
tblQuestion, which resolves the many-to-many relationship between
tblAnswerSession and tblQuestion. Additionally, there's a one to many
relationship with tblPossibleAnswers, because any of the possible answers
could be answered to any of the questions.

I sense that this is a complete answer for somebody who knows what
they are doing. Left join means answer number 2 on the "type of Join
dialog", no? Is a 'join" number 1?

A Join is the way that the query decides which records in each of the two
tables are relevant. So, what you get when you normally add two tables to a
query grid is an inner join (only returns records where both tables have
records in the joined field). A Left or Right Join is a join where all
records on one side are returned. So, if you want to get a set of records
for a session that hasn't happened yet, you need to return all Questions
even if there are no records in tblAnswerSession that match yet to use for
the form to fill in.

If possible, could you tell me in more layman's terms such as here are
the relationships and the corresponding joins.

Could I then use the form wizard to make a form which has tblClient,
tblQuestions, tblAnswerSessions and tblAnswers? Pretty it up
afterwards? Or is the wizzard too limited?

You would need to use a form for tblClient and a subform for
tblAnswerSessions, plus a subform for the join to provide a set of Question
records related to tblAnswerSession to fill in.

HTH;

Amy

 

[men]

Well, this time I ate dinner before heading for the computer. Your
reply was better than dinner and desert together!

Thank you very much for sticking with me!!

I only just perused your answer before this reply. It looks like you
covered it!

I am going to print out your reply and start working off of it. I will
definitely write back to you success or failure. Of course I am hoping
for success so that you can get on with your life

Big smiles here, Amy, and a big, heartfelt "Thank You!"

 

[men]

I worked on it a couple of more hours last night. The tables and the
form are up and I think I am very close to having it work.

I don't have enough time this A.M. to get very specific but thought I
run by you the general problem I kept running into last night.

I can't get the form to display the questions from tblquestions nor
the possible answers from answertext.

I am trying to have the subfrorm show all 22 questions at once or at
least a few at a time and the possible answers (mutually exlusive
option group, if possible). All the data entry person would need to do
is put checkmarks to the answers as they appear on the completed by
client paperform he/she is needing to enter.

That's where I got stuck last night and I hope you could shed some
light on it for me, once more.

Thanks, Amy. I can't check for messages until after I get home this
evening but I'll head straight here when I come home.

Thanks.

I guess you could say that all three are the primary key, in that you would
not expect to have more than one answer to any one question in a given
session. If you wanted a single key for some reason, you could give it
another Autonumber field. However, unless you're hand writing SQL, I'm not
sure there's much advantage to it, since it does not provide a foreign key
to any other table.

 

[Amy Blankenship]

I worked on it a couple of more hours last night. The tables and the
form are up and I think I am very close to having it work.

I don't have enough time this A.M. to get very specific but thought I
run by you the general problem I kept running into last night.

I can't get the form to display the questions from tblquestions nor
the possible answers from answertext.

I am trying to have the subfrorm show all 22 questions at once or at
least a few at a time and the possible answers (mutually exlusive
option group, if possible). All the data entry person would need to do
is put checkmarks to the answers as they appear on the completed by
client paperform he/she is needing to enter.

The first thing you'll need to do is build a query that joins tblAnswers
with tblQuestion. If you don't see a join line between QuestionID and
QuestionID, drag the QuestionID from tblQuestion to tblAnswers.
Double-click the join line and change it to "Show all rows from tblQuestion
and only the rows from tblAnswer where the joined fields are equal. Drag
all fields from both tables to the query...any fields you don't want users
to see, such as QuestionID and AnswerID, you can hide on the form. Now run
the query. You should see all of the fields, but AnswerID will be null in
all records. Double-check to make sure that when you look at the record
selector at the bottom of the grid window that you see the * button. This
means that this is a query that you can add records to. You won't actually
be making more rows, but you will be adding records in tblAnswers. If you
don't see that, post back with the SQL from the SQL view of the query and
maybe we can help you sort it.

Now, you should have a form already that is bound to tblClient. Inside it,
you should have a subform for tblAnswerSession. Make the forms bigger so
you have room to add a new subform in your tblAnserSession_Subform. Use the
subform control with the wizard turned on to add a new subform. Select your
query as the data source, and add all the fields. If you use datasheet
view, you'll find it easier to just show the fields you want the user to
see, which are the question text and the answer. Also, you don't have to
worry about the fine details of layout and such, because the datasheet
ignores most of that. Once you close the wizard, you'll want to resize the
new subform so it fills the space you made for it. Make sure to close the
wizard into the option that lets you continue to work on the form.

Now, select the AnswerID field and go to Format>Change to>Combobox (A
combobox will work in datasheet view, whereas I don't think radio buttons
will). Open the properties box for the new combobox. On the format tab,
change the column count to 2, and the column widths to 0;1. On the Data
tab, select Table/Query for the Row Source type and select
tblPossibleAnswers as the row source.

I suspect after you have had more than one Questionaire session, you may
wind up getting more records than you bargained for. If that is the case,
you need to select the yourQuery_subform and set a filter on it. Post to
the forms newsgroup if that turns out to be a problem. You may also find
you need a Requery of the lowest subform when you're creating a new Answer
Session to make sure that the AnswerSessionID populates properly in
tblAnswers.

HTH;

Amy

 

[men]

Wow! It looks like this is it!

I just finished putting the form together per your very detailed
(thank you) instructions and it looks like it will work!

I am so shocked that I finally seem to have a working database that I
haven't dared to try it out - I wanted to write you a quick Thank You
before I start playing

I'll make an exact copy of te whole DB before I mess around - this
thing is too precious to loose.

Amy, I hope that you know that I appreciate your help, time and
persistence tremendously. You are awesome, you are da bomb!!!

I will report back after I have taken your DB design on a thorough
test drive.

Thanks a million!

 

[Amy Blankenship]

Wow! It looks like this is it!

I just finished putting the form together per your very detailed
(thank you) instructions and it looks like it will work!

I am so shocked that I finally seem to have a working database that I
haven't dared to try it out - I wanted to write you a quick Thank You
before I start playing

I'll make an exact copy of te whole DB before I mess around - this
thing is too precious to loose.

Amy, I hope that you know that I appreciate your help, time and
persistence tremendously. You are awesome, you are da bomb!!!

I will report back after I have taken your DB design on a thorough
test drive.

Thanks a million!

 

[men]

I suspect after you have had more than one Questionaire session, you may
wind up getting more records than you bargained for. If that is the case,
you need to select the yourQuery_subform and set a filter on it. Post to
the forms newsgroup if that turns out to be a problem.

You are right. There are and will be several hundred questionaires. I
don't understand, yet, what you mean "yourQuery_subform" and what
filter to set. Frankly, I have not thought deeply about what you are
telling me here for I am still busy trying to understand why and how
your construct so far is working i.e. I am still trying to learn and
understand everything about why "my" database is working now
I understand the tables by now but I still don't have a complete grasp
how the query you had me do ties into the form or, to put it another
way. how to use queries in general to make forms behave.

You may also find
you need a Requery of the lowest subform when you're creating a new Answer
Session to make sure that the AnswerSessionID populates properly in
tblAnswers.

Yes, this is one of those "use query to make form behave" scenarios I
mentioned above. This is actually one I am trying to figure out right
now. It would be nice to have the SessionID prepopulate the subform
which I have yet to figure out how to do.

I also have received your last post with the and I thank you for
that.

My answer: much>