can i do an update query that replaces the oldest dated record with a new one?

[cherold]

I'm trying to create a "most recently opened" record list. So I'd like to just replace the oldest record in that table with the newest one. So:

UPDATE [recently opened] SET id = 1, date = Now() WHERE date = (OLDEST DATE IN TABLE)

Is there a way to do this?

[ranman256]

make Q1 to get the oldest record,
select [ClientID] from table where Min([dateFld))

then the update query, Q2, that uses Q1 in the IN statement of the ID:
UPDATE TABLE SET TABLE.DateEarned = Date() WHERE (((TABLE.ClientID) In (SELECT [ClientID] FROM q1)));

note: this is simliar to the method used in FIND DUPLICATES query. Use the wizard to build a Find dupe query and then look at the sql.