How do I show only 3 records per customer?

[toddaway]

Sorry if this is the wrong place to post...maybe in reports?



Ultimately, I need a report that shows CUSTOMER NAME, DATE1, DATE2, DATE3

For simplicity, say the main table just contains 2 customers and the dates they made purchases, 1 entry for each date. So Smith has 20 dates that he made purchases and Jones has 15 dates.

It is easy enough to run a query with "Smith" as a criterion and limit the results to 3 (I just need the last 3 dates). But then how would I make the report list those 3 dates in a row as opposed to 1 per page?

Also, what if I had 100 employees and I didn't want to make a query for each one with their last name as the criterion?

Thanks.

[pbaldy]

See if this helps:

http://allenbrowne.com/subquery-01.html#TopN

[toddaway]

Thanks, pbaldy.

[pbaldy]

No problem, and welcome to the site by the way!

[toddaway]

So I've been trying to get this to work with my specific database, and I just can't wrap my head around it for some reason. My brain gets lost in the subquery. Maybe because I'm working with 2 tables and a query and the code gets confusing. Anyway, from the site you linked to:

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

I don't understand

 WHERE Dupe.CustomerID = Orders.CustomerID

It's like saying where 1 = 1, so why bother? Or why not use any field from the table since they will all be equal?
It seems like it could just be SELECT TOP 3 FROM <the whole table sorted by date>.

[orange]

You are using the same table (Orders) twice - once as Orders and once as Dupes. The database needs the alias to keep things organized.

You're getting the TOP 3 Orderids based on OrderDate desc and OrderId desc
Then you are getting the other info based on these OrderIds.

I'm sure there are more elegant explanations, but that's what it's saying to me.
Hope it helps.

[pbaldy]

Sorry, missed the follow up question. Orange explained it as well as or better than I could. All I can say is it works. I use this method in a car maintenance db among others, and it works flawlessly.

[toddaway]

I understand that it's getting results from results, I just don't understand why it needs to reference the table twice in the subquery anyway. It's not like it needs to find a previous record to compare a current record to, like an explanation I saw somewhere. I guess I'm too thick. But I do appreciate you trying to clarify things for me.

[toddaway]

Well, I figured out a working SQL statement. It does not use the "alias" method as given in the example. I got it from method 1 on this page: http://support.microsoft.com/kb/153747

[orange]

Glad you have it sorted out.
How about showing us the final query you used, and the data in and out?
Someone else may benefit from what you have found.