Need to return the 5 most recent records for each field in a table

[Guest]

Hi All,
I know that a query can return the top 5, 10, 15 values, etc., but can it
return the top 5 for each record in a table? Example: a one to many
tblProduct to tblTransaction relationship, can I return the 5 most recent
transactions from tblTransaction for each tblProduct record?
Thanks for any assistance.

Gina

 

[Guest]

If you either have a datetime tag to sort descending on or a sequential
numbered field like an autonumber to sort descending.

 

[Guest]

I do have an autonumber field, TransactionID in the tblTransaction. I also
have a TransactionDate field in that table.

 

[Guest]

Ok, just sort the TransactionDate field descending by opening your TOP 5
select query in design view and in the Sort: row type Descending.

 

[Guest]

Thanks for your reply. I tried that, however the query returns only 5
records, and I had wanted to return the most recent 5 records for each
product in tblProduct. tblProducts has around 100 records, therefore the
query should return ~500 records.

Thanks again.

 

[Guest]

Post your SQL and I will take a look.

Thanks for your reply. I tried that, however the query returns only 5
records, and I had wanted to return the most recent 5 records for each
product in tblProduct. tblProducts has around 100 records, therefore the
query should return ~500 records.

Thanks again.

 

[Guest]

Try this --
SELECT T.TransactionID, T.ProductID, T.TransactionDATE, (SELECT COUNT(*)
FROM [tblTransaction] T1
WHERE T1.TransactionID = T.TransactionID
AND T1.TransactionDate <= T.TransactionDATE) AS Rank
FROM tblTransaction AS T
WHERE ((((SELECT COUNT(*)
FROM [tblTransaction] T1
WHERE T1.TransactionID = T.TransactionID
AND T1.TransactionDate <= T.TransactionDATE))<=5))
ORDER BY T.TransactionID, T.TransactionDATE;

 

[Guest]

Sorry to be so dumb, but do I substitute my table names for all those T's and
T1's???

At any rate, here's my SQL:
SELECT tblProducts.ProductID, tblProducts.ItemNo,
tblTransactions.TransactionDate, tblTransactions.TxType, tblTransactions.Qty
FROM tblProducts INNER JOIN tblTransactions ON tblProducts.ProductID =
tblTransactions.fkProductID;