Finding 2nd to last event

E

Ed Finley

How would I find the second to last event per person in a table that has
dates, events and names. In other words I'm trying to group by name, and
instead of Max for the Date totals, I'm looking for the second to last date.
Thanks,
Ed
 
P

PC Datasheet

From my file:
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com

Use this code to find the next-to-last order for all customers, and it's
simple to change it to find the third from last, forth from last etc.
orders.

SELECT t1.CustomerID, t1.OrderID, t1.OrderDate
FROM
TblOrder AS t1 INNER JOIN TblOrder AS t2
ON t1.OrderDate <= t2.OrderDate
AND t1.CustomerID = t2.CustomerID
GROUP BY t1.CustomerID, t1.OrderID, t1.OrderDate
HAVING COUNT(*) = 2
ORDER BY t1.CustomerID
 
E

Ed Finley

Thanks. I appreciate the help.
Ed
PC Datasheet said:
From my file:
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
[email protected]
www.pcdatasheet.com

Use this code to find the next-to-last order for all customers, and it's
simple to change it to find the third from last, forth from last etc.
orders.

SELECT t1.CustomerID, t1.OrderID, t1.OrderDate
FROM
TblOrder AS t1 INNER JOIN TblOrder AS t2
ON t1.OrderDate <= t2.OrderDate
AND t1.CustomerID = t2.CustomerID
GROUP BY t1.CustomerID, t1.OrderID, t1.OrderDate
HAVING COUNT(*) = 2
ORDER BY t1.CustomerID
Click to expand...
 
Top