For Duane Hookom

[dee]

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[Duane Hookom]

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.

 

[dee]

Thank you for your responses.

I have been looking at your tables and how they are related. I'm not
totally clear on the roles of the tables that are not related, such as
tblKeywords, tlblLookup, etc.

Would you mind clarifying this?

Thank you.

 

[Duane Hookom]

These are a symptom of a lazy programmer. I probably converted an
un-normalized survey where separate fields were used for each question.
tblLookup was most likely imported and then used to create the
tblResponsesList.

I might have a potential use for tblKeyWords but it has escaped my memory.
There is probably lots of other remnants and garbage that should have been
thrown out ;-).

 

[dee]

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

 

[Duane Hookom]

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.

 

[dee]

Yes, I realize that and I thank you.

What I am trying to do, though, is create my own database through trying to
understand how yours is constructed, which I can't do through filling in your
forms.

I also have a lot of extra information (tables) to add that contain regions,
interviewer information, etc.

 

[dee]

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!

 

[Duane Hookom]

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>

 

[dee]

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!

 

[Duane Hookom]

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[dee]

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[Duane Hookom]

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[dee]

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[Duane Hookom]

I'm not sure how much help I can be since you have changed table and field
names and other stuff that I can't see. I don't know what you are attempting
to do when you get the error message. What form is open and what are you
trying to do?

BTW: I am leaving on a 4 day fishing trip to Lake of the Woods (northern
Minnesota) so any reply may be delayed.
--
Duane Hookom
Microsoft Access MVP

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee


:

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[dee]

Hi Duane,

I am actually working with your database - unchanged. I am using the
frmSurveys..

When I add an additional question in the sub-form to any survey there is no
problem. However, as soon as I try to add to the list of answers I get the
error message that indicates can't change the record as a related record is
required in the tblQuestions.

I re-downloaded your database to make sure I hadn't touched it and this kept
happening.

In mine, the same thing happens. If I do it manually via the tables, there
is no problem, but from the form it gives me this message.

Hope you have a great time on your fishing trip!


--
Thanks!

Dee

I'm not sure how much help I can be since you have changed table and field
names and other stuff that I can't see. I don't know what you are attempting
to do when you get the error message. What form is open and what are you
trying to do?

BTW: I am leaving on a 4 day fishing trip to Lake of the Woods (northern
Minnesota) so any reply may be delayed.
--
Duane Hookom
Microsoft Access MVP

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP


:

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee


:

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.
3. I assume that I will need a record for each and every response to each
and every question.
4. I have headings at the beginning of sets of questions with the various
questionnaires - Section 1 "xxx", followed by questions pertaining to that
section, etc. Would I include that as a level in my tblQuestions?

Any guidance would be GREATLY appreciated!

Thank you so much!

 

[Duane Hookom]

One issue might be is the current new question record is not saved when you
click the button to add possible responses. If you add to save the record
prior to openning the form, you should be okay. Otherwise, move to a previous
question then the new question prior to adding possible responses.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I am actually working with your database - unchanged. I am using the
frmSurveys..

When I add an additional question in the sub-form to any survey there is no
problem. However, as soon as I try to add to the list of answers I get the
error message that indicates can't change the record as a related record is
required in the tblQuestions.

I re-downloaded your database to make sure I hadn't touched it and this kept
happening.

In mine, the same thing happens. If I do it manually via the tables, there
is no problem, but from the form it gives me this message.

Hope you have a great time on your fishing trip!


--
Thanks!

Dee

I'm not sure how much help I can be since you have changed table and field
names and other stuff that I can't see. I don't know what you are attempting
to do when you get the error message. What form is open and what are you
trying to do?

BTW: I am leaving on a 4 day fishing trip to Lake of the Woods (northern
Minnesota) so any reply may be delayed.
--
Duane Hookom
Microsoft Access MVP

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee


:

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP


:

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee


:

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

The possible answers are numbered 1, 2, 3, etc., but may be 1 - male, 2 -
female on one question and 1 - Yes, 2 - No on another, etc. There are two or
three numbers that may appear that are always the same: 50 - NR and 51 -
Don't know. These answers are always numbered 50 and 51, even if they follow
answers 1, 2, 3, 4 (50, 51).

My questions, at the moment are:

1. Does my structure, so far, look sound?
2. Why in your tblQuestions do you have RespnsType, RspnsLength, etc. I
would hav thought they would be in the Reponses table.

 

[dee]

Thanks for taking the time to respond, especially when getting ready to go on
your trip.

Have a great time!


--
Thanks!

Dee

One issue might be is the current new question record is not saved when you
click the button to add possible responses. If you add to save the record
prior to openning the form, you should be okay. Otherwise, move to a previous
question then the new question prior to adding possible responses.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I am actually working with your database - unchanged. I am using the
frmSurveys..

When I add an additional question in the sub-form to any survey there is no
problem. However, as soon as I try to add to the list of answers I get the
error message that indicates can't change the record as a related record is
required in the tblQuestions.

I re-downloaded your database to make sure I hadn't touched it and this kept
happening.

In mine, the same thing happens. If I do it manually via the tables, there
is no problem, but from the form it gives me this message.

Hope you have a great time on your fishing trip!


--
Thanks!

Dee

I'm not sure how much help I can be since you have changed table and field
names and other stuff that I can't see. I don't know what you are attempting
to do when you get the error message. What form is open and what are you
trying to do?

BTW: I am leaving on a 4 day fishing trip to Lake of the Woods (northern
Minnesota) so any reply may be delayed.
--
Duane Hookom
Microsoft Access MVP


:

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee


:

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP


:

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee


:

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

 

[dee]

Hi Duane,

Hope you had a great fishing trip and caught some big ones!

I have been working away and my database is really coming along nicely. One
thing that I was wondering. In your table that contains questions, you have
fields such as ResponseType, LmtLst, ResponseValid, QstnMast, etc. I am
unclear as to how exactly these fields are ultimately used.


--
Thanks!

Dee

One issue might be is the current new question record is not saved when you
click the button to add possible responses. If you add to save the record
prior to openning the form, you should be okay. Otherwise, move to a previous
question then the new question prior to adding possible responses.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

I am actually working with your database - unchanged. I am using the
frmSurveys..

When I add an additional question in the sub-form to any survey there is no
problem. However, as soon as I try to add to the list of answers I get the
error message that indicates can't change the record as a related record is
required in the tblQuestions.

I re-downloaded your database to make sure I hadn't touched it and this kept
happening.

In mine, the same thing happens. If I do it manually via the tables, there
is no problem, but from the form it gives me this message.

Hope you have a great time on your fishing trip!


--
Thanks!

Dee

I'm not sure how much help I can be since you have changed table and field
names and other stuff that I can't see. I don't know what you are attempting
to do when you get the error message. What form is open and what are you
trying to do?

BTW: I am leaving on a 4 day fishing trip to Lake of the Woods (northern
Minnesota) so any reply may be delayed.
--
Duane Hookom
Microsoft Access MVP


:

I hesitate to post again. It seems that I'm perhaps requiring too much
assistance, we one respondent mentioned in reply to a question I had posted.

I'm really just trying to understand and gain knowledge.

In any case, I really appreciate your help and patience.

I was having a problem when filling out the survey questions and list of
responses - kept getting the message "can't change the record as a related
record is required in TblQuestions". I thought it was me, but went back to
the original AYS database and seem to have the same problem.

I imagine it's code that says to update the TblQuestions before input of
response list entries. Can you point me in the right direction?

Thanks

--
Thanks!

Dee


:

You are not blind. The documentation is a little out of version with the app.
You can delete records in the usual way be using the menus.

--
Duane Hookom
Microsoft Access MVP


:

Duane, I am trying to use your survey database to better understand it.

I may be blond and really tired, but I don't see the buttons on the Survey
Design to delete the survey and all related questions, as mentioned in the
accompanying Word document.

Am I missing something? Help!

--
Thanks!

Dee


:

It's up to you where you want to store the information. You could actually
add questions that would allow storing the phone number, address, etc. If
this is information that may be used in more than one survey, I would
consider placing the information in a separate table and link it to
tblSrvRespns.

--
Duane Hookom
Microsoft Access MVP


:

I am working with your forms and thing are coming along. A question would be:

Your tblSrvRespns contains only SrvID, RspnsID and RspnsName. My question is:

I have a lot more data for each respondent - phone number, address, etc.,
etc. They are identified more by a number than a name, BTW. Should I
include all of this in the tblsSrvRspns or have a separate table that is
related to this one?
--
Thanks!

Dee


:

Check the On Current event of the subform where the answers are entered:
<code>
Rspns.Requery
Me![Rspns].LimitToList = Me!LmtLst
If Me![RspnsType] = 1 Then
Me![Rspns].RowSourceType = "Value List"
Me![Rspns].RowSource = "Yes;No"
Else
Me![Rspns].RowSourceType = "Table/Query"
Me![Rspns].RowSource = "SELECT tblResponsesList.Rspns FROM
tblResponsesList WHERE
((tblResponsesList.QstnID=[Forms]![frmSurveyResponses]![sfrmResponses].[Form]![QstnID]));"
End If
If IsNull(Me![RspnsValid]) Then
Me![Rspns].ValidationRule = ""
Me![Rspns].ValidationText = ""
Else
Me![Rspns].ValidationRule = Me![RspnsValid]
Me![Rspns].ValidationText = Me![RspnsValid]
End If
</code>
--
Duane Hookom
Microsoft Access MVP


:

I am trying to figure out the "limit to list" field. I have many anwers
that are not limited to list - the respondent may select one of the list
answers, or may write their own answer.

Can you point me in the right direction regarding how to handle this? You
mentioned having the "limit to list" field in your questions table, which I
see fine. However, I'm looking for how you use this in a data entry form,
but can't seem to figure it out.

Thanks!
--
Thanks!

Dee


:

You should be able to just stick with the forms for entering your survey
design. Everything you should need to do to encorporate your survey should be
available from the survey design form.
--
Duane Hookom
Microsoft Access MVP


:

Hi again,

I guess some of my confusion comes from the fact that your database is set
up to create the surveys, etc., whereas I am starting mine from scratch.

I am trying to understand the role of each table to start with and which
tables are not necessary for me, in view of the fact that users won't be
setting up surveys.

The purpose of the database is to input the data from surveys that have been
completed over the summer. We will take the hard copy and input via forms to
the various tables.

For detailed statistics, we will be exporting to another software.

Thank you so very much for your assistance.

--
Thanks!

Dee


:

I don't believe I store the answer_id in the tbl_responses since I often
allow free text entry. I have a "limit to list" field in my questions table
that I use in my data entry form. Using the answer_id assumes all possible
answers for each question will be in the tbl_answers.

I have some fields in my table of questions that I could use for additional
functionality. For instance, if I required a numeric response type, then I
would know that I could average or total the field.

Is qstnaire_code unique in tbl_questionnaires? Otherwise I would use
qstnaire_id in tbl_questions.

Each answer from each participant for each question for each survey will
create a new record in the responses table.

You could use the levels much as I have to group questions.
--
Duane Hookom
Microsoft Access MVP


:

I have downloaded your excellent survey database.

I am building a survey database from scratch and am still in the planning on
paper stage.

My tables are as follows:

tbl_questionnaires
qstnaire_id (PK)
qstnaire_name
qstnaire_code
qstnaire_description

tbl_participants
ppt_id (PK)
ppt_no
ppt_given_name
ppt_family_name
ppt_id_no
ppt_date_of_birth
ppt_gender
pregnant
rltnshp_to_hd_of_hshld
ppt_house_no
ppt_phone_no

tbl_questions
question_id (PK)
qstnaire_code (FK)
question_number
question_text

tbl_answers
answer_id
answer_no
question_id (FK)
answer

tbl_responses
response_id (PK)
question_id (FK)
answer_id (FK)
participant_id (FK)
qstnaire_date
qstnaire_start_time

tbl_interviewers
interviewer_id (PK)
interviewer_no
Interviewer_given_name
Interviewer_family_name

tbl_household_relationships
hshld_relationship_id (PK)
hshld_relationship_category (FK)
hshld_relationship_no
hshld_relationship_description

The questions are made up of the qstnaire_code field and 1, 2, 3, etc.
There are no sub questions.

While many questions limit the answer to one listed, some require text
reponse from the individual being interviewed.

 

[Fargo Jim]

Duane,

If I may sneak in on this thread...

Thanks for making "At Your Survey" available for others to use and learn
from. I'm using it for a 47 question survey for my church.

On the Suvey Response Entry screen, I can't seem to move beyond the 8th
question. Any clue as to what I'm doing wrong?

Thanks much!

--Jim