asked on

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

Dale Fye

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'

jtovar3

ASKER

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 Fye

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'

jtovar3

ASKER

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 Fye

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?

Nick67

Hey,

How is this different from the question you asked at
https://www.experts-exchange.com/questions/26909268/how-can-i-update-a-table-based-on-the-result-of-a-count-query.html

Just wondering :)

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial