Recordset is not updateable.

[Lee Stafford via AccessMonster.com]

I have a table that I need to update and I have changed this arrangement
too many times to count. I think this is the closest that I have come yet.
Here is the SQL.

SELECT qa.WONumber,
job.CorrespondingType,
sec.SectionDesc,
[ques.SectionID]&'.'&[ques.QuesNum] AS Line_Number,
ques.Question,
qa.Response
FROM ((((tbl_questions ques
INNER JOIN tbl_section sec
ON ques.sectionid = sec.sectionid)
INNER JOIN TBL_JOB_TYPE job
ON sec.sectionid = job.sectionid)
INNER JOIN tbl_QA_WO_INFO info
ON job.jobtype = info.jobtype)
LEFT OUTER JOIN tbl_QandA qa
ON info.wonumber = qa.wonumber)

Everythingelse is linked and will fill in all but the response field.
Which is the field I want to update. Can someone help me? I have read so
much on updateable queries and I haven't grasped the concept yet.

tia,

Lee

 

[Van T. Dinh]

Queries that involve more than 2 Tables are often non-updateable.

Check Access Help topic "Updatable Queries" which explains when a Query is
updateable and gives a few techniques to convert a non-updateable Query to
become updateable.

 

[Jack MacDonald]

Access will make a query non-updateable when it cannot reliably
determine which one of several potential records are intended to be
updated. I have always found it useful to make a COPY of the query,
then delete tables one-by-one to determine exactly which relationship
is causing the non-updateablility (???). From there, it is usually a
matter of examining the indexes to ensure that records are properly
identified with Unique indexes.

I have a table that I need to update and I have changed this arrangement
too many times to count. I think this is the closest that I have come yet.
Here is the SQL.

SELECT qa.WONumber,
job.CorrespondingType,
sec.SectionDesc,
[ques.SectionID]&'.'&[ques.QuesNum] AS Line_Number,
ques.Question,
qa.Response
FROM ((((tbl_questions ques
INNER JOIN tbl_section sec
ON ques.sectionid = sec.sectionid)
INNER JOIN TBL_JOB_TYPE job
ON sec.sectionid = job.sectionid)
INNER JOIN tbl_QA_WO_INFO info
ON job.jobtype = info.jobtype)
LEFT OUTER JOIN tbl_QandA qa
ON info.wonumber = qa.wonumber)

Everythingelse is linked and will fill in all but the response field.
Which is the field I want to update. Can someone help me? I have read so
much on updateable queries and I haven't grasped the concept yet.

tia,

Lee

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

Everythingelse is linked and will fill in all but the response field.
Which is the field I want to update. Can someone help me? I have read so
much on updateable queries and I haven't grasped the concept yet.

Is it OBLIGATORY to construct one grand master query to update
everything? Bear in mind that Access is unusual among databases in
that it lets *two* table queries be updateable, not to speak of
five-table queries! It may just be that Access won't LET you make such
a deep query updateable.

Ordinarily if you're doing manual updating, an arrangement of Forms
and Subforms would be preferable. Might that be an option in this
case?


John W. Vinson[MVP]