[AccessD] Most efficient means to retrieve most recent record

Fri Jun 3 15:21:44 CDT 2011

<...suppose Bill's not been imported...>
Not an issue unless you've included name and title in the index.
However, remember that when you do a seek on an index key you also have to
test with NoMatch. 
For example, set a compound index "ixNameTitle" on Employee Name + Title.
Then open your recordset...

Set rst = db.openrecordset(TableName, dbOpenTable)
rst.index = "ixNameTitle"
Rst.seek "=", "Bill", "Accountant"
If NOT rst.NoMatch then
	<do something with Bill>
End If 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
(VBACreations.Com)
Sent: Friday, June 03, 2011 7:30 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

OK, I think I understand this fulle ... and will be on the lookout for some
code which will create a descending index using DAO within VBA since this is
a code-created table. But what I don't understand now is, suppose Bill's not
been imported in the last 25 imports. 

BTW, if we already know we want Bill the Accountant, why have you written a
recordset loop ... can't we use SEEK here as in your earlier post?

Thanks. 
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Friday, June 03, 2011 10:16 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

Hi Bill. By "timestamp" I was referring to your "WhenRecordImported" field.
I picked 5/10/2011 just as an example.
If you put a descending index on WhenRecordImported and set that index on
the recordset, the newest record will be the first record in the recordset.

1. Set an index called "ixWhenRecordImported" on the field
WhenRecordImported, descending 2. set rst = db.openrecordset(TableName,
dbOpenTable) ... note: the recordset type must be dbOpenTable. it doesn't
need to be explicitly stated.
3. rst.index = "ixWhenRecordImported"
The recordset will now be sorted in descending order by date/time, so your
first occurrence of Bill the Accountant will be the most recent... 
4. Do While not rst.eof
	If rst!Name = "Bill" and rst!Occupation = "Accountant" then
		<GoTo somewhere and do something>
	End if
	Rst.MoveNext
   Loop

If you want to find a specific date to begin your search, use the seek
method to move the recordset starting point to that date, then start your
loop.

HTH,
Darrell

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
(VBACreations.Com)
Sent: Friday, June 03, 2011 12:53 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Most efficient means to retrieve most recent record

Hi Darrell,

I am still trying to digest most of your reply, thanks, but in the meantime
Let me make sure we understand one another.

My objective is to find the latest occurring record where (for example) Name
= "Bill" and Occupation = "Accountant"; Suppose I have 3 records like this,
timestamped 

Name	Occupation	WhenRecordImported
    Bill	Accountant	1/15/2011  3:52AM
    Bill	Accountant	1/17/2011  1:52AM
    Bill	Accountant	1/22/2011  2:52AM

When you say index on WhenRecordImported and use SEEK, what is driving your
example date of 5/10/2011 - why are you picking that? I have no knowledge of
what timestamps are in the table when I employ this device.

Thanks.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darrell Burns
Sent: Thursday, June 02, 2011 9:21 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [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"

After you do a seek, you have to test for a NoMatch. For example...

Rst.index = "ixStrDate"
Rst.seek "=", "20110602"
If NOT rst.nomatch then
	Do While not rst.eof
		If rst!strDate > "20110602" then
			Exit do
		End if
		<your code>
		Rst.movenext
	Loop
End If

Sorry bout dat.
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

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com