[AccessD] Most efficient means to retrieve most recent record

Hi William.
I think you'd get the fastest retrieval by putting an index on your
timestamp field. Let's call it "ixTimeStamp". Then after you set your
recordset, set the index as... 
1. rst.index = "ixTimeStamp" 
This will sort your recordset on the index. Then do a seek. For example, to
search the recordset for stuff that happened on 5/10/2011... 
2. rst.seek ">=", #5/10/2011# (I think that's the correct syntax for a date
value)
Then start a loop on the recordset and exit the loop when the date
changes...
3. do while not rst.eof
	If rst!TimeStamp > #5/10/2011# then
		Exit do

You may also want to consider splitting the date and time fields. I usually
convert my dates to a YYYYMMDD string format and index the string field. For
example...
Rst.index = "ixStrDate"
Rst.seek, "=", "20110510"

HTH,
Darrell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
(VBACreations.Com)
Sent: Thursday, June 02, 2011 5: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