Previous record 1

[OU18]

All,
I have a need to retrieve the last record for a particular account id, and the second to last record for the same account id.

the tables involved are

TblAccountInformation
Fields - AccountID, AccountName

tblInvoiceTransactions
Fields - AccountID(links to tblAccountInformation),TransactionID,RecievedDate.

The field i need to retrieve is the RecievedDate. I need the Lastdate and second to last date retrieved.

Sample data

Account ID TransactionID RecievedDate
1 250 4/21/04
1 265 6/21/04
1 400 7/21/04
1 433 9/21/04

Based on the example above, i need to get the following information

Account ID TransactionID RecvdDate
1 400 7/21/04
1 433 9/21/04

How can I do this. I know how to get the last date, i have achieved this by running a query and using the (Last)option to get the last date for each account id. How can I get the second to last date.

Thanks for your help everyone
Steve

 

[lupins46]

To get the latest 2 transactions for a given account you can use this query. Enter the accountid when prompted.

SELECT TOP 2 tblinvoicetransactions.*
from tblinvoicetransactions
WHERE (((AccountID)=[enter AccountID]))
ORDER BY tblinvoicetransactions.RecievedDate DESC;

 

[OU18]

Lupins46,
Thanks for the quick response. That works great, but, I have some 35,000 records. The user wants to be able to just run the query and it will automatically find the last two records per account id, for all Account IDs in the table. Is this possible to do.

Thanks
Steve

 

[lupins46]

Possiblyit'll take a while to run)....


SELECT a.*
FROM tblinvoicetransactions AS a
WHERE a.RecievedDate In (Select top 2 Recieveddate from tblinvoicetransactions where a.accountid = tblinvoicetransactions.accountid order by recieveddate desc)
ORDER BY a.accountID;

 

[OU18]

Lupins46,
That did the trick, I appreciate your help.
Steve