update query, marking the record with maximum value (1 Viewer)

I have a table, each record is information about a sample. Each sample belongs in one of several different groups. I want to mark the sample within each respective group that was taken most recently, and I want to do it by modifying a yes/no field using an update query.

Attached is a simplified version of the problem and my two attempts at making an update query. The table I am trying to modify is called "ShotTable"

View attachment Database2.mdb

I keep getting the following message
"operation must use an updateable query"

Argh.

Any ideas about how I might accomplish my task would be appreciated. Thanks.

Well, this isn't a direct answer to your question, but in my view you shouldn't try to store this info and keep it updated. I'd use this method if you're trying to get info related to the most recent record:

http://www.baldyweb.com/LastValue.htm

My daughter just visited Oman, and loved it.

I used a method similar to what you described to select the info. But it seems I'm unable to update it with a query. I supposed I can do it with the help of some VBA code.

I know it seems like a strange thing to do... actually we go though and edit the chosen sample on a case by case basis, it is just very useful to mark final record as a starting point and then modify our choices from there. We are dealing with 15,000 of these "groups" on a daily basis.

I'm glad you daughter loved Oman I love it too!

Okay, then a workaround is DISTINCTROW:

UPDATE DISTINCTROW ShotTable INNER JOIN GoodShots ON ShotTable.Group = GoodShots.Group SET ShotTable.isVoid = True
WHERE ((([ShotTime]<>[GoodShot])=True));

Hallelujah! It works. Thank you!!

Happy to help!