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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
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.
Dale FyeOwner, Developing Solutions LLCCommented:
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'
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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 FyeOwner, Developing Solutions LLCCommented:
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 :)
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.