[AccessD] Most efficient means to retrieve most recent record

That's probably the way I would do it too.

-- 
Stuart

On 3 Jun 2011 at 13:42, Steve Schapel wrote:

> 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.
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>