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.

Who is Participating?
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
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'
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.
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'
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.
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?

How is this different from the question you asked at

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