MS Access Functions

Return the Top Records in Each Group in Microsoft Access

Last updated: August 29, 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.

Make tie handling explicit

A top-per-group query needs a stable ordering rule. Add a unique key after the business sort column so equal dates or amounts do not produce unpredictable results.

Test groups with fewer than the requested number of rows and groups tied at the boundary. Decide whether the requirement is exactly N records or every record sharing the Nth value.

  • Index the grouping column.
  • Use a deterministic final sort key.
  • Check performance on the largest group.

Use realistic sample data that includes Nulls, ties, and boundary dates. Save a copy before converting a SELECT query into an action query, and compare row counts before relying on the result.

Continue with running totals, monthly grouping, and duplicate records.

Practical implementation check

Build and save the SELECT version before using the result in a form, report, export, or action query. Test records containing Nulls, duplicate sort values, missing dates, and boundary dates. For larger tables, compare execution with appropriate indexes and avoid wrapping an indexed field in a formatting function when ordinary range criteria can do the same work.

Reference: Microsoft Access TOP clause reference.

admin

admin

Related Posts