CURRENT Average problem

[Dave Reardon]

I have a database with two tables of relevance to the current problem. One is
a record of WORK TITLES, with a PK autonumber field. The second contains a
list of STUDENT WORK, which are populated with a studentId, and workId from
the first table. I have created a query linking the two tables, and a form in
which I can enter marks for each student.

What I seem to have trouble doing is getting the form to put the current
value of the average for the piece of work into the WORK TITLES table (a
field showing in the form). I have played around with the field in which I
enter the actual marks, and set the on Change property to
DAvg("[Percentage]","ReviewPieceOfWork-Q","[ID]= [ID1] and [Percentage]<>0")
where [ID] is the autonumber value, and ID1 is the alias of the field on the
form (in other words, it calculates the average only for that piece of work).
However, it seems to give me the value it was before the last change. If I
put a local unbound field onto the form with the same syntax, it returns a
different (correct) value. I have even tried changing the value to that of
the unbound field but to no avail.

Any ideas?

 

[S.Clark]

Explain more about the problem, and less about the solution you've attempted
to implement. Give examples.

 

[Klatuu]

"What I seem to have trouble doing is getting the form to put the current
value of the average for the piece of work into the WORK TITLES table "

Don't do it. Carrying calculated values in a table violates database
normalization rules. Only calculate the value when you need to display it
in a form, a report, or a query for a user to see.

 

[John W. Vinson]

What I seem to have trouble doing is getting the form to put the current
value of the average for the piece of work into the WORK TITLES table

Well...

Don't.

If you store the average for the piece of work in WORK TITLES, that value will
be *wrong* the moment you add, delete, or edit the value in the related table.

Don't store it at all; just calculate it when it's needed.