[AccessD] Most efficient means to retrieve most recent record

William

Modification of your Method 2:

SELECT TOP 1 FROM YourTable
WHERE Blabla
ORDER BY YourDate DESC

Regards
Steve

-----Original Message----- 
From: William Benson (VBACreations.Com) 
Sent: Friday, June 03, 2011 12:12 PM 
To: 'Access Developers discussion and problem solving' 
Subject: [AccessD] Most efficient means to retrieve most recent record 

Hello,

In an Access table where I import and append very similar data routinely, I
stamp every record with the same ImportTime at time of import. 

I am wondering the most efficient means of getting at the latest record
matching certain criteria?  I see three ways. 


METHOD 1
   (1) Set a recordset = the entire table ordered descending, with no WHERE
clause
    (2) use Rst.FindFirst and put the criteria here as arguments 


METHOD 2
   (1)  Use an ORDER BY (desc) clause, select all the records
     where those fields have the values I specify
   (2) Use the first record in the recordset.

METHOD 3
   (1) Select the max date where those fields have the values I specify
   (2) Select "the" record where those fields have the values I specify 
     AND
     Date = Date found from (1)   .... to me this seems unlikely to be
fastest, but who knows.