Update field/table from a derived field in another query

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
 
E

Evi

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Inserting values from previous row into null cells 1
Trying to update multiple fields in the same record (same table) 2
Update Query based on calc. Field in another query 2
Update query. Within the same table: populating field 2 from field 6
Update Field from Query Result 1
sum field from one query to update a field in a table 2
Update query with autonumber field 4
Selectively Update Field in Table 2

Top