most recent 5 records

[Guest]

I have a form/table for customers and one for orders. They share a common id.
When I run a query to join them, I get multiple records for each customer
depending on the number of orders they have placed. Is there a way to have it
only return the last 5 records with the most recent order? I cannot query by
dates, because customer #1 might have placed last order 1 year ago, while
customer
#2 may have placed 15 orders during that time or customer #3 may have 100
orders until today. I only need the most recent 5 orders per customer for my
report. Any assistance would be greatly appreciated.

 

[Allen Browne]

You can do this easily in a form or report, by using a subform/subreport.

Main form bound to Customers table.
Subform bound to a query like this:
SELECT TOP 5 Orders.* FROM Orders ORDER BY OrderDate DESC;

 

[Guest]

How about if I want to report all customers , but just limited 5 last order
of each customer.
Simon:

 

[Allen Browne]

The subform should show the 5 most recent orders for the customer in the
main form.

If you want to do it without the subform, use a TOP 5 subquery in the WHERE
clause of the main query. Details in Method 1 of this article:
How to Create a "Top N Values Per Group" Query
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039