CODEWITHSUNDEEP
ms-accessaggregate-functionsmax
Shiken
Shiken

Reputation: 3

Aggregate function error in Access update query using Max()

UPDATE tblFiles INNER JOIN dbo_tblStats ON tblFiles.FileName = dbo_tblStats.Video SET tblFiles.CurRecord = Max([dbo_tblstats.stopframe])
WHERE (((tblFiles.Progress)<90));

Intent of the code is to take the highest frame number from one table, and replace the current frame number in another table with it, on entries under 90% completion. A select query returns the Max fine, but doing an update throws the error:

"You tried to execute a query that does not include the specified expression 'CurRecord' as part of an aggregate function."

Help with this would be appreciated, thanks.

Upvotes: 0

Views: 1312

Answers (1)

HansUp
HansUp

Reputation: 97131

See whether a DMax expression (see DMin, DMax Functions) gets what you need from dbo_tblStats ... ask for the max stopframe where Video matches the current FileName value.

Assuming Video and FileName are both text data type, try this query.

UPDATE tblFiles
SET CurRecord =
        DMax(
            "stopframe",
            "dbo_tblStats",
            "Video='" & FileName & "'"
            )
WHERE Progress<90;

Upvotes: 1

Related Questions