A
Amy Blankenship
I have a database which (to give you a boiled down summary) contains a bunch
of multiple choice questions, with responses A-E, represented in the
database with 1-5. In order to keep an accurate history of what they did,
how and when, I have a session table which stores information on when they
entered and left a given question. When they select a new response, a new
QuestionResponse record is created, bound to the Session they were in. So
far so good.
And all this was easy as pie when a selection of one response deselected any
other response they might have made--we just looked for the top
QuestionResponseID in the most recent Session and we knew what the current
state of their responses should be. Now, the client has asked for multiple
responses to be allowed for. This means that I need to be able to look at
the most recent response for _each_ potential response for the question. I
have been able to create a query that does this vertically, but what I'd
like to be able to do is something like this:
Select QuestionID, A
Some subselect based on my query), B: (some other
subselect on that query), etc FROM Question.
The subquery is a parameter query, so I cannot use a crosstab query. This
is my best guess at the syntax,
SELECT DISTINCT Question.QuestionID, NZ((SELECT IsOn FROM
rptResponseByQuestion_Distractor as rrb WHERE DistractorNum = 1 and
rrb.QuestionID =Question.QuestionID ),0) AS A, NZ((SELECT IsOn FROM
rptResponseByQuestion_Distractor as rrb WHERE DistractorNum = 2 and
rrb.QuestionID = Question.QuestionID ),0) AS B
FROM Question;
but when the query runs, I get "At most one record can be returned by this
subquery"
That doesn't make any sense to me, since it is tied to the QuestionID in the
main record.
Does anyone have any ideas?
Thanks;
Amy
of multiple choice questions, with responses A-E, represented in the
database with 1-5. In order to keep an accurate history of what they did,
how and when, I have a session table which stores information on when they
entered and left a given question. When they select a new response, a new
QuestionResponse record is created, bound to the Session they were in. So
far so good.
And all this was easy as pie when a selection of one response deselected any
other response they might have made--we just looked for the top
QuestionResponseID in the most recent Session and we knew what the current
state of their responses should be. Now, the client has asked for multiple
responses to be allowed for. This means that I need to be able to look at
the most recent response for _each_ potential response for the question. I
have been able to create a query that does this vertically, but what I'd
like to be able to do is something like this:
Select QuestionID, A
Some subselect based on my query), B: (some othersubselect on that query), etc FROM Question.
The subquery is a parameter query, so I cannot use a crosstab query. This
is my best guess at the syntax,
SELECT DISTINCT Question.QuestionID, NZ((SELECT IsOn FROM
rptResponseByQuestion_Distractor as rrb WHERE DistractorNum = 1 and
rrb.QuestionID =Question.QuestionID ),0) AS A, NZ((SELECT IsOn FROM
rptResponseByQuestion_Distractor as rrb WHERE DistractorNum = 2 and
rrb.QuestionID = Question.QuestionID ),0) AS B
FROM Question;
but when the query runs, I get "At most one record can be returned by this
subquery"
That doesn't make any sense to me, since it is tied to the QuestionID in the
main record.
Does anyone have any ideas?
Thanks;
Amy