Solved

Access Query Update able

Posted on 2016-07-31
5
47 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 34

Accepted Solution

by:
PatHartman earned 400 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 142

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 34

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 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now