Access Query Update able

I'm using the code below to count records and update that count to a table however I get the following error message:
Error
UPDATE tblGoalsComplete INNER JOIN tblGoals ON (tblGoalsComplete.OpDiv = tblGoals.OpDiv) AND (tblGoalsComplete.[Goal Month] = tblGoals.[Goal Month]) SET tblGoalsComplete.[Actual Count] = Count([tblGoals]![Actual Month])
WHERE (((tblGoals.Status)="Complete"));
shieldscoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PatHartmanConnect With a Mentor Commented:
I'll bread the answer into several parts because once the error you have is resolved, you will get a different error.
1.  To aggregate data in a query, all columns in the result set must be aggregated.  Therefore, if you are not counting, summing, etc., you must include the column in a Group By clause.
2. Once you fix the aggregation, you will get a different error and this one will tell you that your query is not updateable.  That because once you aggregate data, the query engine cannot identify individual records and so that makes the query not updateable.

Storing aggregated data violates first normal form and so relational databases make no accommodation for helping you to do this.    Once you save an aggregated value, it becomes out of date as soon as someone updates the underlying data.  That is why you should not do it.  But, there are reasons to do it, primarily in a data warehouse where the user isn't ever going to update the underlying data since it is replaced en masse on a set schedule.

3. To store this value (and I cannot emphasize strongly enough that you should NOT store it - you should calculate it as needed), first create a temp table with the aggregated values and then use an update query that takes the counts from the temp table and updates the permanent records.

4. And finally, I'm not sure you are using the Count(*) function correctly.  Count(somefield) counts all selected rows where somefield is not null so normally if you are counting rows, you would use Count(*) which is also more effective since it can frequently be calculated by using an index whereas Count(somefield) always requires a full table scan.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what about this
UPDATE tblGoalsComplete
SET tblGoalsComplete.[Actual Count] = ( select Count([tblGoals]![Actual Month])
from tblGoals
where (tblGoalsComplete.OpDiv = tblGoals.OpDiv)
AND (tblGoalsComplete.[Goal Month] = tblGoals.[Goal Month])
 and ((tblGoals.Status)="Complete")
);
0
 
PatHartmanCommented:
"Bread" should obviously be "Break"
0
 
shieldscoAuthor Commented:
Guy as Pat mentioned query is not updateable
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
then I see only the use of DCount function, inefficient for large scale, and can only confirm the reasoning of my collegue above.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.