Showing Largest And Smallest and Max in Access

[sap1958]

I need to also do the following in MS Access (using Access2003)

1. Show largest gift transaction and the date
2. Show the smallest gift transaction and the date
3. Show the first gift transaction and the date
4. show the last gift transaction and date

I have the giftid, giftamount and giftdate as parameters

 

[dhookom]

Try something like:

SELECT *
FROM transactions
WHERE 
GiftID = (SELECT TOP 1 GiftID
 FROM Transactions
 ORDER BY GiftAmount DESC, GiftID)
OR
GiftID = (SELECT TOP 1 GiftID
 FROM Transactions
 ORDER BY GiftAmount, GiftID)
OR
GiftDate = (SELECT Max(GiftDate)
 FROM Transactions)
OR
GiftDate = (SELECT Min(GiftDate)
 FROM Transactions)

Duane
Hook'D on Access
MS Access MVP