JET SQL query

Question

Hi Guys,

I have a table in access which I'm trying to query with Jet SQL via an ADO connection.

Each record in the table is for a serial number, the results of an assessment against it and the date it was assessed.

The primary key is a autonumber [ID] field.

A serial number can be in the table multiple times so can have multiple records, but the date and [ID] will be different.

I'm trying to pull all fields into a recordset, but only those records which are the most recent entry of each serial number. So, if the serial number 12345 occurs multiple times, I only want the most recent one (most recent date or largest [ID]).

I've tried using "SELECT [Serial Number], MAX([ID]) FROM tblAssessment GROUP BY [Serial Number]", but this doesn't allow me to retrieve all the fields for a given record.

Does anyone know of a good way to do this please?

Answer 1

Try creating a query (qryLastRecord) like this --

SELECT [Serial Number], MAX([YourDateField]) AS Max****YourDateFieldFROM tblAssessment GROUP BY [Serial Number];

Save the query and use as a LEFT JOIN as in this query --

SELECT [tblAssessment].* 

FROM tblAssessment LEFT JOIN qryLastRecord ON tblAssessment.YourDateField = qryLastRecord.Max****YourDateField  AND     tblAssessment.[Serial Number]  = qryLastRecord.[Serial Number];

EDITS --