Microsoft access sql query to update last record

Question

Below is my query where I'm fetching the top two records, but the last record is missing some columns: Status, Purpose, Location, and Remarks. I intend to copy these values from the second-to-last record. Furthermore, I want to update the last record only if its Status is "Gate Out" and change the Status value to "Gate In". These changes should be applied permanently to the TextfileData table.

SELECT TOP 2 TextfileData.Empcardno, TextfileData.Date, TextfileData.Status, TextfileData.Purpose, TextfileData.Location, TextfileData.Remarks, *
FROM TextfileData
WHERE (((TextfileData.Empcardno)=[forms]![Main Check In Out Master]![Empcardno]))
ORDER BY TextfileData.Date DESC;

Output of above query

Output I Want

Answer 1

Using your first query, create and save two new queries returning the first and the second record:

SELECT TOP 1 *
FROM YourQuery
ORDER BY [Date] DESC;

SELECT TOP 1 *
FROM YourQuery
ORDER BY [Date] ASC;

Then create an update query that - apart from the Status field - updates the fields of the first query with the values of the second.