Unanswered: Acc 2003 Problem with Query to return latest record
I have a table (caseStageTran) which stores proceedings of a case in the court. caseStageTran
Now I need a query which would display only the latest of the case details. It is quiet possible that a case is posted for evidence on say 1/10/2010 and on that day it is decreed. So my caseStageTran would hold both data in diff rows. Little complexity is, if the caseStageID is within 35,55,65,70 then that whole case record should not be part of the query result!
If you record the time along with the Casedate (using Now() function), instead of date alone as you do now, it will not be a problem to identify and filter the latest record.
If this is done then you can create a Total Query grouped on CaseID and Max(CaseDate) columns which will give you a recordset that identifies the latest records. This Query you can use to link, with the Main Recordset in a Query, on CaseID to extract the latest transaction records.
Or you may create a separate field to record Data entry/Editing activity Date/Time through the Before Update EventProcedure it will serve the same purpose. You may use one of the two methods in your projects and it is a must.
You may go through the following blog post to know how important it is: