Solved

Access Query Update able

Posted on 2016-07-31
5
63 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 38

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 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 38

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 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

623 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