Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Access Query Update able

Posted on 2016-07-31
5
Medium Priority
?
66 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 39

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 39

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

670 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