Using Average in an update query 2

[jmcg]

Looking to use and average value to update a set of records but get an error when trying to execute.
When I view the query it works fine but will not update.
Query:
UPDATE xTargets INNER JOIN Output_CV_Total ON xTargets.PERSONCCAT = Output_CV_Total.PERSONCCAT SET Output_CV_Total.FYTarget = Avg([xtargets].[TOTAL])
WHERE (((Output_CV_Total.AREA)='Total'));
Error:
You tried to execute a query that does not include the specified expression 'FTTarget' as part of an agregate function.

Any help much appreicated

 

[PHV]

You may try something like this:

UPDATE Output_CV_Total
SET FYTarget = DAvg("TOTAL","xTargets","PERSONCCAT='" & [PERSONCCAT] & "'")
WHERE AREA = 'Total'

If PERSONCCAT is defined as numeric get rid of the single quotes in the DAvg call.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jmcg]

Exactly what I needed thanks