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_InProgress90DaysPas
t' 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')))
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.