Basic Visual Basic Recordset Ignorance

[herbivorous]

After a few days of wandering around in the wasteland of my own ignorance (holy crow, it's big . . .), I'm looking for help. Probably embarrassingly simple help, but I can't figure it out from Access's Help.

I am writing functions to generate, based on a primary key ID number, a string containing the amount, date, and source code of a donor's last donation, and largest donation. I can do this nesting DMax and DLookup functions, but it is slow, as it involves multiple calls to a Payments table that contains some 30K entries.

I am guessing (tell me if I'm wrong) that it would be faster if I first established a recordset containing only the payment records for the one donor in question, then doing the domain functions on that recordset alone. If true, how do I define and open this recordset with an SQL statement such that I can then use it in DLookup and DMax.

(One complicating issue is that for the maximum donation, we want to see the most recent donation if there is more than one donation of the maximum amount -- if I gave $400 in 98 and 02, the 02 should come up, which means (I think -- I haven't written this one yet, just the easier Last one) without using a recordset ordered in descending date order, I either nest a DMax in a Dlookup in a DMax in a Dlookup, or base the whole thing on a query, which also seems inefficient.

I also welcome suggestions of brighter ways to handle the whole issue.

(Basic structure explanation, all payments are in tblPayments, which links to tblContacts by autonumbered ID field.)

Thanks

 

[jebry]

Hi!

Queries are probably your fastest solution. Try this:

query1

Select * From YourTable Order By YourDateField Descending

query2

Select PKID, Max(DonationField) As LargestDonation, First(DonationField) As LastDonation From query1 Group By PKID

hth


Jeff Bridgham
bridgham@purdue.edu