Display the most recent Records

[QA_Compliance_Advisor]

I have a table called tbl_DCR_status which its function to the track changes within a process and its purpose is to provide a more comprehensive audit trail and is displayed like a dairy per lines. I can't attached the the file (keeps failing to upload - assuming its too big).



I ahve used MAX and Last Function from the Totals function, however, the query still shows all records but they are grouped, however, I need only the most recent. the reason I need the most recent is only to display this info when someone wants to info on the current status of the tracking issue. I have also tried two queries:

(one) with DCR Number (Tracking Number) and the Dates of the record change (and the max/ Last function is used in this field) SQL

SELECT tbl_DCR_Status.[DCR Number], Max(tbl_DCR_Status.[Assigned Date]) AS [MaxOfAssigned Date]
FROM tbl_DCR_Status
GROUP BY tbl_DCR_Status.[DCR Number];

and the second query has the the full tbl_DCR_Status on display with the MaxOfAssigned Date included. SQL

SELECT qry_DCR_Status.[DCR Number], tbl_DCR_Status.[DCR Stage], tbl_DCR_Status.[Assigned To], tbl_DCR_Status.[Assigned Date], tbl_DCR_Status.[Action Deadline], tbl_DCR_Status.[Action Date Deadline], tbl_DCR_Status.[Received Response], tbl_DCR_Status.[Received Response Date], tbl_DCR_Status.NOTES
FROM tbl_DCR_Status, qry_DCR_Status;


is there any solutions?

[pbaldy]

You're close; you don't have a join between the table and query in the second query:

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

[QA_Compliance_Advisor]

well thank you very much - i ahve got closer to what i want. however, there is still some duplicates
New Second Query SQL

SELECT qry_DCR_Status.[DCR Number], tbl_DCR_Status.[DCR Stage], tbl_DCR_Status.[Assigned To], tbl_DCR_Status.[Assigned Date], tbl_DCR_Status.[Action Deadline], tbl_DCR_Status.[Action Date Deadline], tbl_DCR_Status.[Received Response], tbl_DCR_Status.[Received Response Date], tbl_DCR_Status.NOTES
FROM tbl_DCR_Status RIGHT JOIN qry_DCR_Status ON (tbl_DCR_Status.[Assigned Date] = qry_DCR_Status.[MaxOfAssigned Date]) AND (tbl_DCR_Status.[DCR Number] = qry_DCR_Status.[DCR Number]);

[pbaldy]

If changes can happen on the same day, I might add the time to the date field so it knows which is more recent.

[QA_Compliance_Advisor]

@pbaldy - Thank you for the advice. It gave me the idea to have a field called "Created" in the table. And have it as a date time, default value as "Now()" or "Date()" (depending on your preference) and set the date picker as never. So now once a create a new record the created field will now input the date and time (to the second) and then I should be able to do the Max function as previous stated in posts.

[pbaldy]

No problem.