?
Solved

Access Query Update able

Posted on 2016-07-31
5
Medium Priority
?
71 Views
Last Modified: 2016-07-31
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"));
0
Comment
Question by:shieldsco
  • 2
  • 2
5 Comments
 
LVL 41

Accepted Solution

by:
PatHartman earned 1600 total points
ID: 41736508
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41736510
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
 
LVL 41

Expert Comment

by:PatHartman
ID: 41736511
"Bread" should obviously be "Break"
0
 

Author Comment

by:shieldsco
ID: 41736527
Guy as Pat mentioned query is not updateable
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 41736528
then I see only the use of DCount function, inefficient for large scale, and can only confirm the reasoning of my collegue above.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Implementing simple internal controls in the Microsoft Access application.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses
Course of the Month3 days, 9 hours left to enroll

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question