Double Action Query

[jaydwest]

I need to get the SumOfPaymentAmount as well as LastPaymentAmount and LastPaymentDate for an InvoiceID from an Invoice table. I can do this with an aggregate query, but it is oh so slow even for not so large a recordset. I can also do this by first creating a recordset with Payment data for the Invoice in descending Date order to pick up the Last PaymentDate and PaymentAmount. Then I run an Aggregate query on the resulting temp table to get the Sum. Since the Temp Table has a lot less records than the complete Invoice Recordset, this works much faster.

Somehow it seems a little bit kludgy to generate the intermediate recordset.

Does anyone have a slicker way of doing this? Bonus Points for the answer!

Thanks for your help.

 

[Bello]

Jaydwest,

I wonder if this is a better way...

1) Create a recordset.
2) Includes following criteria as your record source.
3) Replace 'YourInvoice' with a control value or any invoice no.
4) The very first record in recordset would give you the answer.
-----------------------------------------------------------------------------------

MyCriteria = "SELECT PaymentDate, PaymentAmount,
(SELECT sum(PaymentAmount) FROM Invoice
WHERE (Invoice_no='YourInvoice')) AS SumOfPaymentAmount
FROM Invoice
WHERE ((Invoice_no)='YourInvoice')
ORDER BY PaymentDate DESC;

SET MyRecordset = MyDB.OpenRecordset(MyCriteria, dbOpenDynaset)

MyRecordset.Movefirst

LastPaymentDate = MyRecordset("PaymentDate")
LastPaymentAmount = MyRecordset("PaymentAmount")
SumOfPaymentAmount = MyRecordset("SumOfPaymentAmount")

------------------------------------------------------------------------------------

Regards,
Bello