How to select top 5 results

[metamp]

I have 2 tables, both tables have a common field "First Name"
the left table has thousands of records and the right table has the "First Name field" grupped so there is only a dozen first names

I want to select 5 Records from the left table per each "First Name" in the Right Table

another words the right table has first names

Peter
John
Arthur

I wana select 5 records with "First Name" Peter , 5 Johns and 5 Arthurs from the left table

Can this be done in one query or I have to run the query many times with different where clause ?

any help appreciated

 

[dhookom]

How about defining which 5? In order to run this type of query, you must define the order the records are displayed.

For instance, if you want to select the most recent orders for each CustomerID in the Orders table of Northwind sample MDB. This would use the OrderDate to find the 5.

SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate
FROM Orders
WHERE (((Orders.OrderID) In (SELECT TOP 5 OrderID FROM Orders O where O.CustomerID =Orders.CustomerID ORDER BY OrderDate DESC)))
ORDER BY Orders.CustomerID, Orders.OrderDate;

Duane
Hook'D on Access
MS Access MVP