using lookup fields in queries

[Guest]

I have a single table database (access 2003) with client survey info. Many of
the questions are simple yes/no answers. I used the lookup wizard to create
yes/no combo boxes for these questions. When i try to run a query to count
the yes' or the no's i either get a datatype mismatch or i just get a count
of all records in the database. Any suggestions as to what I'm doing wrong?

 

[Jeff L]

It depends on how your set up your yes/no fields. If you set them as
text, then when you query for Yes, it needs to be in quotes, "Yes". If
you set it up as Yes/No, then no quotes. By your description, it
sounds like you set them as text.

Hope that helps!

 

[John Vinson]

I have a single table database (access 2003) with client survey info. Many of
the questions are simple yes/no answers. I used the lookup wizard to create
yes/no combo boxes for these questions. When i try to run a query to count
the yes' or the no's i either get a datatype mismatch or i just get a count
of all records in the database. Any suggestions as to what I'm doing wrong?

If you used Microsoft's misdesigned, confusing, and vexing Lookup
Wizard then you no longer have one table. You have as many tables as
you created lookup fields.

And if you're using one field per question, you may be on the wrong
track (unless you will *never* need to add, remove, or change a
question). You might want to see Duane Hookum's excellent "At Your
Survey" sample database for a different, much more flexible,
normalized design:

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

I can't tell you offhand how best to fix your existing query since I'm
not sure of the actual structure of your tables, but normalizing the
table structure would be a good way to keep you from digging yourself
deeper into an inadequate design!

John W. Vinson[MVP]

 

[Guest]

Thanks for the link, I got a good idea about how to create a table for lookup
values, it's still a bit over my head. If I'm just working off one table for
my survey do i only need one more table to put the lookup values in? And how
do i get the answers to store in the correct table? I'm a little confused.

 

[John Vinson]

Thanks for the link, I got a good idea about how to create a table for lookup
values, it's still a bit over my head. If I'm just working off one table for
my survey do i only need one more table to put the lookup values in? And how
do i get the answers to store in the correct table? I'm a little confused.

It's clear that you didn't actually work with Duane's sample database.

You would not need ANY lookup tables AT ALL.

Rather than your current "spreadsheet" design with one question per
*field*, Duane's database (and any normalized database) will use one
*record* per question. You will need (at least) three tables:

Questionnaire
QuestionnaireID
<information about this questionnaire as a whole, e.g. who filled it
out, when, etc.>

Questions
QuestionNo
Question <Text>
<perhaps fields for the datatype, e.g. yes/no, number, free text,
multiple choice>

Answers
QuestionnaireID
QuestionNo
Answer

If 100 people fill out the questionnaire, and it has 50 questions, the
Answers table will have 100x50 = 5000 rows. There are NO Lookup fields
used or needed.

John W. Vinson[MVP]

 

[Duane Hookom]

The At Your Survey demo does have a "lookup" table that can be used to
provide a list of possible responses for each different question. It is
something like:

QuestionNo PossibleResponse
=========================
1 Red
1 Blue
1 Green
2 Apple
2 Pear
3 Chevrolet
....

 

[Guest]

Hi, I have been trying to deal with a potential survey for most of the day.
I keep returning to Duane Hookom's survey database to try and figure it out
but ...

I have pretty much imitated his form and subform for response entry. What's
different is that my survey starts from a Company Table listing address etc
that I was hoping to just have as a form and my survey be a subform (linked
by Company ID). I have a question table, an answer table and a response
table. The answer table lists more than one possible answer for the various
question and I was hoping to create drop down boxes for the response.

All the responses would go to a Response table. Some times the respondent
would be able to select more than one response so there could be more than
record per question.

I have the continuous form for the series of questions working but the drop
down lists that feed from the Answer table isn't working. Last night I
thought I found someone asking this question but I couldn't find it today. I
would like the combo box on the subform to just provide the possible answers
for that question. Here is what I have attempted

SELECT Answers.Answer FROM Answers
WHERE
(((Answers.QuestionID)=[Forms]![frmForm5]![sfrmForm1].[Form]![QuestionID]));


I don't think I am as sophisticated as the other users responding in this
forum, in fact this is my first question. Thank-you for your help. There
are only 10 questions in this survey.

 

[Duane Hookom]

You could add the CompanyID to tblSrvRspns to establish a link.

Regarding you combo box on the subform, you might want to look at my
subform. I used the On Current event of the subform to set the Row Source of
the combo box:

Private Sub Form_Current()
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
End Sub

I think the long RowSource= line from the code could be replaced with:
Me![Rspns].RowSource = "SELECT Rspns FROM tblResponsesList WHERE QstnID=" &
me.QstnID

--
Duane Hookom
MS Access MVP

Hi, I have been trying to deal with a potential survey for most of the
day.
I keep returning to Duane Hookom's survey database to try and figure it
out
but ...

I have pretty much imitated his form and subform for response entry.
What's
different is that my survey starts from a Company Table listing address
etc
that I was hoping to just have as a form and my survey be a subform
(linked
by Company ID). I have a question table, an answer table and a response
table. The answer table lists more than one possible answer for the
various
question and I was hoping to create drop down boxes for the response.

All the responses would go to a Response table. Some times the respondent
would be able to select more than one response so there could be more than
record per question.

I have the continuous form for the series of questions working but the
drop
down lists that feed from the Answer table isn't working. Last night I
thought I found someone asking this question but I couldn't find it today.
I
would like the combo box on the subform to just provide the possible
answers
for that question. Here is what I have attempted

SELECT Answers.Answer FROM Answers
WHERE
(((Answers.QuestionID)=[Forms]![frmForm5]![sfrmForm1].[Form]![QuestionID]));


I don't think I am as sophisticated as the other users responding in this
forum, in fact this is my first question. Thank-you for your help. There
are only 10 questions in this survey.

--
Nancy

The At Your Survey demo does have a "lookup" table that can be used to
provide a list of possible responses for each different question. It is
something like:

QuestionNo PossibleResponse
=========================
1 Red
1 Blue
1 Green
2 Apple
2 Pear
3 Chevrolet
....

 

[Guest]

Thanks for you quick response. I'll continue although I think I can now
conclude that this task is beyond me. I'm not knowledgeable enough on event
procedures or using the various codes. Oddly enough as I read your
discussion on ice cream on another link - that's my area of expertise! I'm
a food scientist who did a Masters in the area of ice cream. Somehow or
another I've ended up using ACCESS alot in my role in food plant labs but I
think this survey is beyond me. I think I may have to do something that
involves a query for each of the answers. I've read a lot of stuff in the
last two days and you are a very helpful contributor. Thanks again.
--
Nancy

You could add the CompanyID to tblSrvRspns to establish a link.

Regarding you combo box on the subform, you might want to look at my
subform. I used the On Current event of the subform to set the Row Source of
the combo box:

Private Sub Form_Current()
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
End Sub

I think the long RowSource= line from the code could be replaced with:
Me![Rspns].RowSource = "SELECT Rspns FROM tblResponsesList WHERE QstnID=" &
me.QstnID

--
Duane Hookom
MS Access MVP

Hi, I have been trying to deal with a potential survey for most of the
day.
I keep returning to Duane Hookom's survey database to try and figure it
out
but ...

I have pretty much imitated his form and subform for response entry.
What's
different is that my survey starts from a Company Table listing address
etc
that I was hoping to just have as a form and my survey be a subform
(linked
by Company ID). I have a question table, an answer table and a response
table. The answer table lists more than one possible answer for the
various
question and I was hoping to create drop down boxes for the response.

All the responses would go to a Response table. Some times the respondent
would be able to select more than one response so there could be more than
record per question.

I have the continuous form for the series of questions working but the
drop
down lists that feed from the Answer table isn't working. Last night I
thought I found someone asking this question but I couldn't find it today.
I
would like the combo box on the subform to just provide the possible
answers
for that question. Here is what I have attempted

SELECT Answers.Answer FROM Answers
WHERE
(((Answers.QuestionID)=[Forms]![frmForm5]![sfrmForm1].[Form]![QuestionID]));


I don't think I am as sophisticated as the other users responding in this
forum, in fact this is my first question. Thank-you for your help. There
are only 10 questions in this survey.

--
Nancy

The At Your Survey demo does have a "lookup" table that can be used to
provide a list of possible responses for each different question. It is
something like:

QuestionNo PossibleResponse
=========================
1 Red
1 Blue
1 Green
2 Apple
2 Pear
3 Chevrolet
....


--
Duane Hookom
MS Access MVP

On Wed, 23 Aug 2006 12:38:01 -0700, robertm76

Thanks for the link, I got a good idea about how to create a table for
lookup
values, it's still a bit over my head. If I'm just working off one
table
for
my survey do i only need one more table to put the lookup values in?
And
how
do i get the answers to store in the correct table? I'm a little
confused.

It's clear that you didn't actually work with Duane's sample database.

You would not need ANY lookup tables AT ALL.

Rather than your current "spreadsheet" design with one question per
*field*, Duane's database (and any normalized database) will use one
*record* per question. You will need (at least) three tables:

Questionnaire
QuestionnaireID
<information about this questionnaire as a whole, e.g. who filled it
out, when, etc.>

Questions
QuestionNo
Question <Text>
<perhaps fields for the datatype, e.g. yes/no, number, free text,
multiple choice>

Answers
QuestionnaireID
QuestionNo
Answer

If 100 people fill out the questionnaire, and it has 50 questions, the
Answers table will have 100x50 = 5000 rows. There are NO Lookup fields
used or needed.

John W. Vinson[MVP]

 

[Duane Hookom]

Good luck. There are a couple of us MS Access MVPs who work for the larger
ice cream manufacturers in the U.S. (and world).


--
Duane Hookom
MS Access MVP

Thanks for you quick response. I'll continue although I think I can now
conclude that this task is beyond me. I'm not knowledgeable enough on
event
procedures or using the various codes. Oddly enough as I read your
discussion on ice cream on another link - that's my area of expertise!
I'm
a food scientist who did a Masters in the area of ice cream. Somehow or
another I've ended up using ACCESS alot in my role in food plant labs but
I
think this survey is beyond me. I think I may have to do something that
involves a query for each of the answers. I've read a lot of stuff in the
last two days and you are a very helpful contributor. Thanks again.
--
Nancy

You could add the CompanyID to tblSrvRspns to establish a link.

Regarding you combo box on the subform, you might want to look at my
subform. I used the On Current event of the subform to set the Row Source
of
the combo box:

Private Sub Form_Current()
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
End Sub

I think the long RowSource= line from the code could be replaced with:
Me![Rspns].RowSource = "SELECT Rspns FROM tblResponsesList WHERE QstnID="
&
me.QstnID

--
Duane Hookom
MS Access MVP

Hi, I have been trying to deal with a potential survey for most of the
day.
I keep returning to Duane Hookom's survey database to try and figure it
out
but ...

I have pretty much imitated his form and subform for response entry.
What's
different is that my survey starts from a Company Table listing address
etc
that I was hoping to just have as a form and my survey be a subform
(linked
by Company ID). I have a question table, an answer table and a
response
table. The answer table lists more than one possible answer for the
various
question and I was hoping to create drop down boxes for the response.

All the responses would go to a Response table. Some times the
respondent
would be able to select more than one response so there could be more
than
record per question.

I have the continuous form for the series of questions working but the
drop
down lists that feed from the Answer table isn't working. Last night I
thought I found someone asking this question but I couldn't find it
today.
I
would like the combo box on the subform to just provide the possible
answers
for that question. Here is what I have attempted

SELECT Answers.Answer FROM Answers
WHERE
(((Answers.QuestionID)=[Forms]![frmForm5]![sfrmForm1].[Form]![QuestionID]));


I don't think I am as sophisticated as the other users responding in
this
forum, in fact this is my first question. Thank-you for your help.
There
are only 10 questions in this survey.

--
Nancy


:

The At Your Survey demo does have a "lookup" table that can be used to
provide a list of possible responses for each different question. It
is
something like:

QuestionNo PossibleResponse
=========================
1 Red
1 Blue
1 Green
2 Apple
2 Pear
3 Chevrolet
....


--
Duane Hookom
MS Access MVP

On Wed, 23 Aug 2006 12:38:01 -0700, robertm76

Thanks for the link, I got a good idea about how to create a table
for
lookup
values, it's still a bit over my head. If I'm just working off one
table
for
my survey do i only need one more table to put the lookup values in?
And
how
do i get the answers to store in the correct table? I'm a little
confused.

It's clear that you didn't actually work with Duane's sample
database.

You would not need ANY lookup tables AT ALL.

Rather than your current "spreadsheet" design with one question per
*field*, Duane's database (and any normalized database) will use one
*record* per question. You will need (at least) three tables:

Questionnaire
QuestionnaireID
<information about this questionnaire as a whole, e.g. who filled
it
out, when, etc.>

Questions
QuestionNo
Question <Text>
<perhaps fields for the datatype, e.g. yes/no, number, free text,
multiple choice>

Answers
QuestionnaireID
QuestionNo
Answer

If 100 people fill out the questionnaire, and it has 50 questions,
the
Answers table will have 100x50 = 5000 rows. There are NO Lookup
fields
used or needed.

John W. Vinson[MVP]