select top x by group in ms access

Solution 1:

MsAccess can have a subquery as its select expression, which can be used to produce a limited rownumber:

SELECT *
FROM (
SELECT TblA.*, 
   (SELECT count(*) from Sheet1 TblB 
    where tblB.GROUP=tblA.GROUP and tblB.Index<=tblA.Index) as ROWNO
FROM Sheet1 TblA) Step1
where Step1.ROWNO<=Step1.A

Biggest caveat is that if two rows of the same group can have the same Index value.

Solution 2:

A single subquery will do:

SELECT 
    YourTable.Group, 
    YourTable.Index
FROM 
    YourTable
WHERE 
    (Select Count(*) From YourTable As T 
    Where T.Group = YourTable.Group And T.Index <= YourTable.Index) <= [A]
ORDER BY 
    YourTable.Group, 
    YourTable.Index DESC;

This assumes, that the values of Index are unique for each group.