Why is this query unupdateable??

[tlyczko]

I get NO error messages about this query from Access 2003.

SELECT tblQuestions.ProgramID, tblQuestions.intTopicSortOrder,
tblQuestions.txtTopic, tblQuestions.intQuestionSortOrder,
tblQuestions.txtQuestion, tblResponses.txtResponse,
tblResponses.AuditID, tblResponses.ResponseID
FROM tblQuestions INNER JOIN tblResponses ON tblQuestions.QuestionID =
tblResponses.QuestionID
GROUP BY tblQuestions.ProgramID, tblQuestions.intTopicSortOrder,
tblQuestions.txtTopic, tblQuestions.intQuestionSortOrder,
tblQuestions.txtQuestion, tblResponses.txtResponse,
tblResponses.AuditID, tblResponses.ResponseID
ORDER BY tblQuestions.intTopicSortOrder,
tblQuestions.intQuestionSortOrder;

I have tried including both DISTINCT and DISTINCTROW and changing the
join properties, right join is wrong, pulls too many records, left join
doesn't make it updateable either. Removing the join doesn't work
either.

Basic idea is to show the questions and topics that go with the
QuestionID in tblResponses.

tblResponses:
ResponseID PK
AuditID FK
AuditInformationID FK
QuestionID FK (this is not defined in the Relationships window, it is
there to show what question's been asked for response)
txtResponse -- the field I want to update to answer the question after
the query is run

tblQuestions:
QuestionID PK
Topic
intTopicSortOrder
intQuestionSortOrder
txtQuestion -- text of the question for people to see
ProgramID
some other fields not used in this query

Do I need to rearrange the fields in the QBE grid?? I've tried this
with some fields and got nowhere.

Thank you, Tom

 

[Crystal]

Hi Tom,

It is not updateable because you can have multiple responses
for each question.

You are also GROUPing...

If you are designing a form, make a main form based on
Questions and a subform based on Responses


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[tlyczko]

Thank you, Crystal!!
After I typed my previous reply, I removed the Group By, I don't know
how it got there, and it became updateable!!
Thank you, Tom

 

[Jerry Whittle]

The GROUP BY kills any chance of be updateable. Distinct will also do so. In
fact to have much of any luck doing it, tblQuestions.QuestionID better be the
primary key.

Go to Help and search on "About updating data". It tells more.

 

[tlyczko]

No, only one reply per question.
The responses table has the questionIDs and other data pre-populated.
I do not want question/answer main/sub forms because I have already
populated the responses table with the right IDs etc. but I will try
removing the group by etc. though.
Thank you, Tom

 

[Randall Arnold]

Sometimes Access throws a GROUP BY in on its own. I've even had it change
Expressions to Group aggregates arbitrarily...

Randall Arnold