question about update query

  • Thread starter Thread starter mcnews
  • Start date Start date
M

mcnews

why does query 2 error out with must be upadatable query?

query 1

SELECT tblCDC.ID, Count(tblCDC.result1) AS CountOfresult1
FROM tblCDC
GROUP BY tblCDC.ID
HAVING (((Count(tblCDC.result1))=1));

query 2

UPDATE tblCompare INNER JOIN qrySum1 ON tblCompare.ID=qrySum1.ID SET
tblCompare.combined = [countofresult1];



tia,
mcnewsxp
 
Because any UPDATE query that has a aggregate query in it (other than in a
where clause) is automatically deemed as not updatable by Access.
Logically, I don't think that makes sense, but ...

Workarounds are to pump the data in query 1 into a table and then use that
table in your update OR use the VBA Domain functions.

UPDATE tblCompare
SET tblCompare.Combined = DCount("Result1","tblCDC","ID=" & tblCompare.ID)

IF the field ID is not numeric, then you need to include quote mark
delimiters, as below

UPDATE tblCompare
SET tblCompare.Combined = DCount("Result1","tblCDC","ID=""" & tblCompare.ID
& """")
 
John said:
Because any UPDATE query that has a aggregate query in it (other than in a
where clause) is automatically deemed as not updatable by Access.
Logically, I don't think that makes sense, but ...

Workarounds are to pump the data in query 1 into a table and then use that
table in your update OR use the VBA Domain functions.

UPDATE tblCompare
SET tblCompare.Combined = DCount("Result1","tblCDC","ID=" & tblCompare.ID)

IF the field ID is not numeric, then you need to include quote mark
delimiters, as below

UPDATE tblCompare
SET tblCompare.Combined = DCount("Result1","tblCDC","ID=""" & tblCompare.ID
& """")
Click to expand...

sweet!
thanks much.
 
Back
Top