I have the following Update query that updates field 'Other' in
Detentions table to true if it meets the criteria set out below.
UPDATE StudentCheckQuery2 INNER JOIN Detentions ON
StudentCheckQuery2.StudentID = Detentions.StudentID SET
Detentions.Other = True
WHERE (((StudentCheckQuery2.Keep)>"0") AND
I need to make one key change now to the query. I need to update all
of the 'Other' fields to true except the last number of 'Keep' records.
So for example, if the value of Keep is 3, then update all of the
'Other' fields in the selected records except for the last three.
I hope this can be done with this same Update query.
I'd really appreciate some help in getting this to work
UPDATE StudentCheckQuery2, Detentions SET [Detentions].[Other] = True
WHERE (((IIf([StudentCheckQuery2].[Keep]>0 And [StudentCheckQuery2].[Keep]<Max([StudentCheckQuery2].[Keep]),True,False))=True) AND (([Detentions].[Detentions])<>[Served]) AND (([Detentions].[Suspended])<>True));