Error in Selecting least 3 rows from each group in ms Access - only least 2 rows are getting selected

Question

I have the data containing 2 columns ID_NO and Date_of_arrival. I want to find the least 3 date_of_arrival for each ID in MS-Access. I have written the code and it is working fine for all the ID_NO except "11111-00000-11", wherein it is selecting only top 2 rows. Please help me rectify my code to select 3 least Date_of_arrival instead of 2.

SELECT a.ID_NO, a.Date_of_arrival
 FROM MainTable a WHERE
 [Date_of_arrival] IN ( SELECT TOP 5 [Date_of_arrival]
 FROM MainTable B WHERE
 a.ID_NO = B.ID_NO
 ORDER BY B.[Date_of_arrival ] ) ORDER BY a.ID_NO, a.[Date_of_arrival];



ID_NO       Date_of_arrival
11111-00000-11  13/11/2019
11111-00000-11  13/11/2019
11111-00000-11  30/12/2019
11111-00000-11  04/02/2020
11111-00000-11  04/02/2020
11111-00000-11  25/02/2020
11111-00000-11  16/03/2020
11111-00000-11  20/03/2020
11111-00000-11  24/04/2020
11111-00000-11  24/04/2020
11111-00000-11  24/04/2020
11111-00000-11  14/05/2020
11111-00000-11  14/05/2020
11111-00000-11  02/01/2019
11111-00000-11  02/01/2019
11111-00000-11  15/02/2019
11111-00000-11  15/02/2019
11111-00000-11  25/04/2019
11111-00000-11  25/04/2019
11111-00000-11  17/05/2019
11111-00000-11  17/05/2019
11111-00000-11  17/05/2019
11111-00000-11  10/06/2019
11111-00000-11  10/06/2019
11111-00000-11  15/07/2019
11111-00000-11  19/08/2019
11111-00000-11  05/09/2019
11111-00000-11  06/09/2019
11111-00000-11  07/10/2019
11111-00000-11  07/10/2019
11111-00000-11  07/10/2019
11111-00000-11  13/11/2019
11111-00000-11  28/05/2020
11111-00000-11  28/05/2020
11111-00000-11  22/06/2020
11111-00000-11  22/06/2020
11111-00000-11  13/07/2020
11111-00000-11  13/07/2020
11111-00000-11  13/07/2020
11111-00000-11  24/07/2020
11111-00000-11  24/07/2020
11111-00000-11  07/09/2020
11111-00001-12  02/01/2019
11111-00001-12  15/02/2019
11111-00001-12  25/03/2019
11111-00001-12  25/04/2019
11111-00001-12  10/06/2019

Answer 1

I suggest that you first do a query in Access that groups by ID, Date of Arrival and Count, having the count >= 3.

SELECT        a.ID_NO
             ,a.Date_of_arrival
             ,count(a.id_no) as CNT
FROM MainTable a 
GROUP BY    a.ID_NO
            ,a.Date_of_arrival
HAVING      count(a.id_no) >= 3

Alternatively

SELECT        a.ID_NO
             ,min(a.Date_of_arrival) as MinDateArrival
             ,max(a.Date_of_arrival) as MaxDateArrival
             ,count(a.id_no) as CNT
FROM MainTable a 
GROUP BY    a.ID_NO                
HAVING      count(a.id_no) >= 3

Answer 2

If you want three distinct values, then you can use:

SELECT a.ID_NO, a.Date_of_arrival
FROM MainTable a
WHERE [Date_of_arrival] IN (SELECT TOP 3 B.[Date_of_arrival]
                            FROM MainTable B
                            WHERE a.ID_NO = B.ID_NO
                            GROUP BY B.[Date_of_arrival ]
                            ORDER BY B.[Date_of_arrival ]
                           )
ORDER BY a.ID_NO, a.[Date_of_arrival];

This will return duplicates, so you will get more than 3 rows for a given id. If you want to guarantee 3 rows, you need an additional column to distinguish the rows. Let me assume you have a primary key, which I'll call pk:

SELECT a.ID_NO, a.Date_of_arrival
FROM MainTable a
WHERE a.pk IN (SELECT TOP 3 pk
               FROM MainTable B WHERE
               WHERE a.ID_NO = B.ID_NO
               ORDER BY B.[Date_of_arrival ]
              )
ORDER BY a.ID_NO, a.[Date_of_arrival];