Query most recent records only

[KevinE via AccessMonster.com]

I am not having much luck with trying to write a query to find the last (most
recent dates) 5 records for ALL names listed in the 'Customers' table.

SELECT tblOrders.*, tblCustomers.ID
FROM tblOrders RIGHT JOIN tblCustomers ON tblOrders.Name = tblCustomers.Name
ORDER BY tblCustomers.ID, tblOrders.Date;

Appreciate any help,
Kevin

 

[Stefan Hoffmann]

hi Kevin,

I am not having much luck with trying to write a query to find the last (most
recent dates) 5 records for ALL names listed in the 'Customers' table.

SELECT TOP 5 O.*, C.ID
FROM tblOrders O
RIGHT JOIN tblCustomers C
ON O.Name = C.Name
ORDER BY C.ID, O.Date DESC;

btw, I'm quite sure that you have normalization problem. Why do you need
to join by name?


mfG
--> stefan <--

 

[John Spencer]

Stefan Hoffman's solution gives you the top five by date in table orders over
all customers (basically 5 orders).

If you mean you want the top 5 orders by date for each customer then that can
also be done using a correlated sub-query in the WHERE clause. That would
look something like the following. I guessed that tblOrders has a primary key
named OrderID. You do need a primary key in the tblOrders.

SELECT tblOrders.*, tblCustomers.ID
FROM tblOrders RIGHT JOIN tblCustomers
ON tblOrders.Name = tblCustomers.Name
WHERE tblOrders.OrderID in
(SELECT TOP 5 OrderID
FROM TblOrders as Temp
WHERE Temp.Name =tblOrders.Name
ORDER BY Temp.Date DESC, TEMP.OrderID)
ORDER BY tblCustomers.ID, tblOrders.Date

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County