How can I update a table with the result of a count query?

Hello Experts!

I am trying to populate an table for metrics "tbl_Metrics" with the result of an count query.

this is the update query I have, and I know for a fact that the sub select statement works since I copied and pasted my 'Count_InProgress90DaysPast'  query exactly.

UPDATE tbl_Metrics SET Result = (SELECT Count(tbl_Observation.Obs_ID) AS Obs_ID
FROM tbl_Audit INNER JOIN tbl_Observation ON tbl_Audit.Audit_ID=tbl_Observation.Audit_ID
WHERE (((tbl_Observation.Due_Date)>DateAdd('d',-9990,Date())) AND ((tbl_Observation.Status)='In Progress')))
WHERE tbl_Metics.Metric_Name='Count_InProgress90Days';

Open in new window


Whenever I run it, I get the "Must be in an updatebale table" error, and I dont know what that means.

I've attached a sample of the database.

Update Query: Update_InProgress90
Table that I want to populate: tbl_Metrics
Field I want filled in: Results

You can also check my query results from my Metrics page. the text field displays the results which should be 5.

Thanks!
AuditTrackerVersionTest2.1.accdb
jtovar3Asked:
Who is Participating?
 
Nick67Commented:
As was noted in that earlier question you cannot directly create an update query that will update with a COUNT.
You can either create a wrapper function in VBA, like was suggested there, or you could save your subquery out separately and call it
The attempt to use an aggregate, either as a COUNT or a DCOUNT, causes the "Must be in an updatebale table" error
You have to get that aggregating to happen outside the update query.

qryThe SavedSubquery
SELECT SomeKeytoRelateToTheMainQuery, Count(tbl_Observation.Obs_ID) AS Obs_ID
FROM tbl_Audit INNER JOIN tbl_Observation ON tbl_Audit.Audit_ID=tbl_Observation.Audit_ID
WHERE (((tbl_Observation.Due_Date)>DateAdd('d',-9990,Date())) AND ((tbl_Observation.Status)='In Progress'

Main query
UPDATE tbl_Metrics SET Result = Select Obs_ID from qryThe SavedSubquery where SomeKeytoRelateToTheMainQuery = SomeFieldInTheMainQuery
0
 
Dale FyeCommented:
Create a query that contains the subquery (without the COUNT) and then save it.

Then use:

UPDATE tbl_Metrics SET Result = DCOUNT("Obs_ID", "queryName")
WHERE tbl_Metics.Metric_Name='Count_InProgress90Days'

If you have different criteria, you might create the saved query, without the WHERE clause, and as that as a criteria string in the UPDATE statement:

strCriteria = Due_Date>DateAdd("d",-9990,Date() AND tbl_Observation.Status='In Progress'
UPDATE tbl_Metrics SET Result = DCOUNT("Obs_ID", "queryName", strCriteria)
WHERE tbl_Metics.Metric_Name='Count_InProgress90Days'
0
 
jtovar3Author Commented:
When I tried your first suggestions with the DCOUNT, a prompt opens with

"Enter Parameter Value tbl_Metrics.Metric_Name" then when i try to enter the name (which is already in the query) it doesn't work and updates all the records in the metrics table.


Also, sorry I am unfamiliar with how to use a criteria string.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
Well, the first problem is because I copied the SQL you had in your original post.  Look at the last line, use use "tbl_Metics.Metric_Name"  rather than "tbl_Metrics.Metric_Name", so fix that first.  For my first solution above, your saved query should look like:

SELECT tbl_Observation.Obs_ID
FROM tbl_Audit INNER JOIN tbl_Observation
ON tbl_Audit.Audit_ID=tbl_Observation.Audit_ID
WHERE tbl_Observation.Due_Date>DateAdd("d",-9990,Date())
AND tbl_Observation.Status='In Progress'
0
 
jtovar3Author Commented:
Oh whoops. completely missed that R. Now I have an issue that the count is actually incorrect. for my actual database, the number should 17 however it only is shows "1"

in the sample DB, i believe the right number is 5.
0
 
Dale FyeCommented:
unfortunately, corporate policy prevents me from downloading your database.

Did you save the subquery as a separate query?  If so, how many records were returned when you ran it?  That should be the number returned by the DCOUNT( ) domain function.

What is the datatype of your [Result] field?
0
 
Nick67Commented:
Hey,

How is this different from the question you asked at
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26909268.html

Just wondering :)
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.