normalization - again

[Brigitte P]

I thought I knew about normalization, but I'm baffled. Our Psychology
deparment want the Mental Status Exam (MSE) in a dbase. The instrument has
normal patient demographic which is read from another dbase. But the exam
part consists of about 50 or more questions. Some have mutually exclusive
forced choice answers, but many need to allow for more than one answer
(selected from a combo or listbox that is based on library tables). For most
question, they also want an "other" field, that would allow narrative info to
be entered. I thought I give the demographic part a unique ID. This ID is
then read into the exam part and is the linked field.
However, if the exam table has 50-80 fields, I just have a flat file. But I
just can't think of a way to break this down; there are no smaller components.
The MSE has domains, e.g., appearance, mood, language skills. Each domain
may have 10 -15 questions. I thought about creating tables for the domains,
and then use the questions from that domain linked by the unique ID field
with the other tables. But this seems the same thing like having all in on
table, just broke up into several tables which doesn't seem to make sense.
To go further, to accomplish data entry, I suppose I need to create subforms
(linked to the demographic parent form). How in the world do I accomodate
50-90 questions in subforms even if I break them down into the before
mentioned domaine tables.
I don't know how to handle this, can anyone help. A form of the MSE can be
found at http://www.psychpage.com/learning/library/advpract/mse.html.
Coordination and gait are an instance where there could be more than one
selection.
Outcome needs to be at this point simply a form that gives the examiner the
data just entered, but we may want to get summary reports at a later date.
Any help is greatly appreciated.
Brigitte P.

 

[mnature]

Try the "At Your Survey" on this page, and see if that will meet some of your
needs:

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

 

[Brigitte P]

I checked, and this is probably too complicated for my skill level. It seems
that the database still has all questions in one table, and I would have done
this the same way, except that I think a 50-90 field table is not normalized.
The word document talks a lot tips about survey design, but I'm not the
survey designer. I use a pre-made survey.
I think I need a different angle on this one, but thanks for your try.

 

[John Vinson]

I checked, and this is probably too complicated for my skill level. It seems
that the database still has all questions in one table, and I would have done
this the same way, except that I think a 50-90 field table is not normalized.

You are correct about a 50-90 field table being non-normal, but you're
mistaken about the structure of At Your Survey. It does have all the
questions in one table - but with *one question per record*. If there
are 90 questions, the table only has two or three fields and 90
records.

John W. Vinson[MVP]

 

[Brigitte P]

I'll work on this tomorrow, and get back with the newsgroup. Please be good
enough to check on me after tomorrow evening (I can't post to the newsgroup
from work), because I'm sure I need more help. I want to do this right,
because I will learn from it for future applications. I do this only here and
there, but I have developed many databases over the years for my workplace
that function very well because of the help from your good people at the
newsgroup. All my thank to all of you.

 

[Brigitte P]

I think I got it, but run into unexpected problems. Since my users need to be
able to have for each questions several options (e.g., Protective Factors
questions could have several answers) and most questions don't have mutually
exclusive answers like male or female, I connected to each question the list
of answers such as 1a Protective Factors Family, 1 b Protective Factors Good
Health etc. Each question is an item on a list (not a field!!), totalling
about 300 questions (records).
So I have a tblDemographics that Includes a SurveyID field. I then have the
second tblQuestions and a third tblResponses with a QuestID a SurvID and
Yes/No response field and one openResponse field (for narrtive comments to
each questions), and a UniqueID Autonumber. Relationships tblDemogaphics
SurveyID tblResponses SurvID 1 to many; tblQuestions and tblResponses QuestId
1 to many. I think this is normalization, isn't it.
However, I run into deep trouble when I do the data entry form. And I know
this should go to the forms forum, but I had asked that you please check back
on me, so I'll post it here.
I created a form to show with Demographics (mainly read from a download),
but it has the SurveyID (one survey per patient). I then created another form
(intended as a subform and show in datasheet view) listing all the questions
and the fields for responses.
I set the default values for the QuestID and the SurvID to the corresponding
tables, which should be activated when the user clicks the checkmark in the
response box to the relevant question. Here are the problems:
1. The form itself displays all questions in datasheet view and
corresponding response option okay, but only as a seperate form not as a
subform (in itself not a big deal, I can lead the user to the form via a
command button, but it should work as subform, shouldn't it).
2. The default values for the SurvID fill only in when I set it in code e.g.
Me!SurvID - [forms]formname.SurveyId on the OnClick Event in the Y/N Response
field. The QuestID fills in nicely from the default value set in the
propertly list once the record is activated - and I understand that a record
must be activated for it all to work. (I had set both in the property list to
the corresponding default values, but it works only for one QuestID)
3. When I unclick the y/N Response field, it doesn't undo the SurvID field,
and doesn't delete the record. Now I can delete the record with the record
selector, but then the user has to refresh, because it also deletes the view
for that question. I then set code to on LostFocus Event of the Y/NResponse
field to If yes then SurvD = formsSurveyID ..... else 0. This would allow be
to run an append query for only those values that are not 0 on the activation
of a save command button. While it probably would work, it seems clumsy to
me.

What I really would like to happen is that the user can see all question in
datasheet view, click the responses and/or write comments (both options
should be possible), check on the end all responses and be able to unselect a
question which should delete the record in the tblResponses. The problem
seems to be in the display of the questions and responses in the same form in
datasheet view.
I know this all could be done nicely by someone with more expertise than I
have, so I appreciate any help.
As always, as an occassional "developer," I have benefitted and learned so
much from you nice people (I seem to understand normalization much better -
got before only to form one normalization, I think).
Thanks.

 

[Duane Hookom]

I'm first confused by "Each question is an item on a list (not a field!!),".
Then, I'm not sure what the "second" and "third" tables are for.

Rather than using several different forms, you might be able to use a single
form with subforms on tabs.
--
Duane Hookom
MS Access MVP

I think I got it, but run into unexpected problems. Since my users need to
be
able to have for each questions several options (e.g., Protective Factors
questions could have several answers) and most questions don't have
mutually
exclusive answers like male or female, I connected to each question the
list
of answers such as 1a Protective Factors Family, 1 b Protective Factors
Good
Health etc. Each question is an item on a list (not a field!!), totalling
about 300 questions (records).
So I have a tblDemographics that Includes a SurveyID field. I then have
the
second tblQuestions and a third tblResponses with a QuestID a SurvID and
Yes/No response field and one openResponse field (for narrtive comments to
each questions), and a UniqueID Autonumber. Relationships tblDemogaphics
SurveyID tblResponses SurvID 1 to many; tblQuestions and tblResponses
QuestId
1 to many. I think this is normalization, isn't it.
However, I run into deep trouble when I do the data entry form. And I know
this should go to the forms forum, but I had asked that you please check
back
on me, so I'll post it here.
I created a form to show with Demographics (mainly read from a download),
but it has the SurveyID (one survey per patient). I then created another
form
(intended as a subform and show in datasheet view) listing all the
questions
and the fields for responses.
I set the default values for the QuestID and the SurvID to the
corresponding
tables, which should be activated when the user clicks the checkmark in
the
response box to the relevant question. Here are the problems:
1. The form itself displays all questions in datasheet view and
corresponding response option okay, but only as a seperate form not as a
subform (in itself not a big deal, I can lead the user to the form via a
command button, but it should work as subform, shouldn't it).
2. The default values for the SurvID fill only in when I set it in code
e.g.
Me!SurvID - [forms]formname.SurveyId on the OnClick Event in the Y/N
Response
field. The QuestID fills in nicely from the default value set in the
propertly list once the record is activated - and I understand that a
record
must be activated for it all to work. (I had set both in the property list
to
the corresponding default values, but it works only for one QuestID)
3. When I unclick the y/N Response field, it doesn't undo the SurvID
field,
and doesn't delete the record. Now I can delete the record with the record
selector, but then the user has to refresh, because it also deletes the
view
for that question. I then set code to on LostFocus Event of the
Y/NResponse
field to If yes then SurvD = formsSurveyID ..... else 0. This would allow
be
to run an append query for only those values that are not 0 on the
activation
of a save command button. While it probably would work, it seems clumsy to
me.

What I really would like to happen is that the user can see all question
in
datasheet view, click the responses and/or write comments (both options
should be possible), check on the end all responses and be able to
unselect a
question which should delete the record in the tblResponses. The problem
seems to be in the display of the questions and responses in the same form
in
datasheet view.
I know this all could be done nicely by someone with more expertise than I
have, so I appreciate any help.
As always, as an occassional "developer," I have benefitted and learned so
much from you nice people (I seem to understand normalization much
better -
got before only to form one normalization, I think).
Thanks.

You are correct about a 50-90 field table being non-normal, but you're
mistaken about the structure of At Your Survey. It does have all the
questions in one table - but with *one question per record*. If there
are 90 questions, the table only has two or three fields and 90
records.

John W. Vinson[MVP]

 

[Brigitte P]

See may post of 6/1/06. I got it by looking very, very closely at your
AYS2000. Also, I somehow wasn't able to access the newsgroup for while but
figured this out also. Today is my day for fiugring things out! Thanks for
sharing your expertise.

I'm first confused by "Each question is an item on a list (not a
field!!),". Then, I'm not sure what the "second" and "third" tables are
for.

Rather than using several different forms, you might be able to use a
single form with subforms on tabs.
--
Duane Hookom
MS Access MVP

I think I got it, but run into unexpected problems. Since my users need to
be
able to have for each questions several options (e.g., Protective Factors
questions could have several answers) and most questions don't have
mutually
exclusive answers like male or female, I connected to each question the
list
of answers such as 1a Protective Factors Family, 1 b Protective Factors
Good
Health etc. Each question is an item on a list (not a field!!), totalling
about 300 questions (records).
So I have a tblDemographics that Includes a SurveyID field. I then have
the
second tblQuestions and a third tblResponses with a QuestID a SurvID and
Yes/No response field and one openResponse field (for narrtive comments
to
each questions), and a UniqueID Autonumber. Relationships tblDemogaphics
SurveyID tblResponses SurvID 1 to many; tblQuestions and tblResponses
QuestId
1 to many. I think this is normalization, isn't it.
However, I run into deep trouble when I do the data entry form. And I
know
this should go to the forms forum, but I had asked that you please check
back
on me, so I'll post it here.
I created a form to show with Demographics (mainly read from a download),
but it has the SurveyID (one survey per patient). I then created another
form
(intended as a subform and show in datasheet view) listing all the
questions
and the fields for responses.
I set the default values for the QuestID and the SurvID to the
corresponding
tables, which should be activated when the user clicks the checkmark in
the
response box to the relevant question. Here are the problems:
1. The form itself displays all questions in datasheet view and
corresponding response option okay, but only as a seperate form not as a
subform (in itself not a big deal, I can lead the user to the form via a
command button, but it should work as subform, shouldn't it).
2. The default values for the SurvID fill only in when I set it in code
e.g.
Me!SurvID - [forms]formname.SurveyId on the OnClick Event in the Y/N
Response
field. The QuestID fills in nicely from the default value set in the
propertly list once the record is activated - and I understand that a
record
must be activated for it all to work. (I had set both in the property
list to
the corresponding default values, but it works only for one QuestID)
3. When I unclick the y/N Response field, it doesn't undo the SurvID
field,
and doesn't delete the record. Now I can delete the record with the
record
selector, but then the user has to refresh, because it also deletes the
view
for that question. I then set code to on LostFocus Event of the
Y/NResponse
field to If yes then SurvD = formsSurveyID ..... else 0. This would allow
be
to run an append query for only those values that are not 0 on the
activation
of a save command button. While it probably would work, it seems clumsy
to
me.

What I really would like to happen is that the user can see all question
in
datasheet view, click the responses and/or write comments (both options
should be possible), check on the end all responses and be able to
unselect a
question which should delete the record in the tblResponses. The problem
seems to be in the display of the questions and responses in the same
form in
datasheet view.
I know this all could be done nicely by someone with more expertise than
I
have, so I appreciate any help.
As always, as an occassional "developer," I have benefitted and learned
so
much from you nice people (I seem to understand normalization much
better -
got before only to form one normalization, I think).
Thanks.

On Mon, 8 May 2006 16:03:01 -0700, Brigitte P

I checked, and this is probably too complicated for my skill level. It
seems
that the database still has all questions in one table, and I would
have done
this the same way, except that I think a 50-90 field table is not
normalized.

You are correct about a 50-90 field table being non-normal, but you're
mistaken about the structure of At Your Survey. It does have all the
questions in one table - but with *one question per record*. If there
are 90 questions, the table only has two or three fields and 90
records.

John W. Vinson[MVP]