MS Access Functions

Return the Top Records in Each Group in Microsoft Access

Last updated: August 28, 2026.

The query Top Values property limits the entire result set. To return the top rows for every customer, category, or region, correlate a TOP subquery with the current group.

Top three sales per customer

Use a unique key as the final sort column so ties produce a deterministic result.

SELECT s.CustomerID, s.SaleID, s.SaleDate, s.Amount
FROM Sales AS s
WHERE s.SaleID IN
(
    SELECT TOP 3 s2.SaleID
    FROM Sales AS s2
    WHERE s2.CustomerID = s.CustomerID
    ORDER BY s2.Amount DESC, s2.SaleID
)
ORDER BY s.CustomerID, s.Amount DESC, s.SaleID;

Most recent record per group

Change TOP 3 to TOP 1 and order by the date descending. Keep the key as a tiebreaker when several rows share the same date.

SELECT o.CustomerID, o.OrderID, o.OrderDate
FROM Orders AS o
WHERE o.OrderID IN
(
    SELECT TOP 1 o2.OrderID
    FROM Orders AS o2
    WHERE o2.CustomerID = o.CustomerID
    ORDER BY o2.OrderDate DESC, o2.OrderID DESC
);

Index and test the grouping fields

Indexes on the group field and ordering field can materially improve a correlated query. Test groups with fewer than N rows and groups containing ties. Decide whether the requirement is exactly N rows or every row tied at the boundary.

Reference: Microsoft Access TOP clause reference.

admin

admin

Related Posts