Update field/table from a derived field in another query

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

Hi,
I've read quite a few of the posts up here already and I seem to have a
mixture of problems and solutions, but none quite fit. So I 'm hoping
someone'll pick up and give me the light bulb on this one.

I have a query (qryOutputGrade), it calculates the standard deviated grade
from a set of class average scores. Fields = Subject_Set, Pupil_Code, StdDiff
and Attain.

I want to update my table (of assesments), tblPrepAssessments, to have the
field GRADE_1 updated to hold the Attain grade from qryOutputGrade, based on
the fact that tblPrepAssessments.Pupil_Code=qryOutputGrade.Pupil_Code AND
tblPrepAssessments.Subject_Set=qryOutputGrade.Subject_Set.

There are blanks in the qryOutputGrade.Attain field as not all scores have
been collected yet.

I've tried the following in QBE:
UPDATE qryPrepAssessments, qryOutputGrade SET qryPrepAssessments.GRADE_1 =
[qryOutputGrade]![Attain]
WHERE (([qryPrepAssessments]![PUPIL_CODE]=[qryOutputGrade]![PUPIL_CODE] And
[qryPrepAssessments]![Subject_Set]=[qryOutputGrade]![Subject_Set]));

this runs a result in the QBE pane, but when trying to use the Run command
gives the following error:
"Operation must use an updateable query"

Have also tried this:
UPDATE qryPrepAssessments, qryOutputGrade SET qryPrepAssessments.GRADE_1 =
[qryOutputGrade]![Attain]
WHERE (([qryPrepAssessments]![PUPIL_CODE]=[qryOutputGrade]![PUPIL_CODE] And
[qryPrepAssessments]![Subject_Set]=[qryOutputGrade]![Subject_Set]));

or is there any other way of doing this? Thanks for your help, greatly
appreciated.

Wendy
 
You Sql is telling you to update qryPrepAssessments. If that is a Totals
query of some sort, then you won't be able to update it either by typing
into it or by an Update query.
What if you change the sql to tblPrepAssessments?



Evi
 
Back
Top