How select query from record count more than 1 and count more than 2 with custom column (Option) in MS Access

I'm trying to select query from record count more than 1 (ID&DATE&INOUT) and count more than 2 (ID&DATE) with custom column (Option) in MS Access

so with "NO" in the option column then the record more than 1 and more than 2 is not included in my sql query code

example for ID 5045 Date 12-Jul-24 should only appear 3 records because they have been marked in the option column NO

Is there something wrong with my SQL code ?

Please Guide me

Table Absen for testing

ID	DATE	TIME	INOUT	OPTION
5045	12-Jul-24	08:11:36	IN
5045	12-Jul-24	08:11:38	IN
5045	12-Jul-24	17:01:01	IN
5045	12-Jul-24	0	OUT	NO
5045	21-Jun-24	08:16:10	IN
5045	21-Jun-24	17:04:45	OUT
5045	22-Jun-24	08:18:14	IN
5045	21-May-24	08:19:25	IN
5045	21-May-24	17:04:40	OUT	NO
5009	29-Apr-24	17:00:00	OUT	NO
5009	29-Apr-24	08:00:00	IN
5009	29-Apr-24	17:00:00	OUT
5011	30-Apr-24	08:00:00	IN
5011	30-Apr-24	08:00:00	IN
5011	30-Apr-24	17:00:00	OUT
5045	11-Sep-24	08:18:14	IN
5045	11-Sep-24	08:19:25	IN

Table MASTERID

ID	NAMEID	POSITIONID
5045	E	STAFF
5009	B	STAFF
5011	D	STAFF

with Code Answer update

SELECT a.ID, MASTERID.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID ON a.ID = MASTERID.ID
WHERE (SELECT COUNT(*)
       FROM ABSEN AS a2
       WHERE a.ID = a2.ID 
         and a.DATE = a2.DATE and a.INOUT = a2.INOUT
         and IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
       ) > 1 OR  (SELECT COUNT(*)
       FROM ABSEN AS a2
       WHERE a.ID = a2.ID 
         and a.DATE = a2.DATE
         and IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
       ) > 2 
ORDER BY a.ID, a.DATE, a.INOUT;

Result From code

ID	NAMEID	DATE	TIME	INOUT
5011	D	30-Apr-24	08:00:00	IN
5011	D	30-Apr-24	08:00:00	IN
5011	D	30-Apr-24	17:00:00	OUT
5045	E	12-Jul-24	17:01:01	IN
5045	E	12-Jul-24	08:11:38	IN
5045	E	12-Jul-24	08:11:36	IN
5045	E	12-Jul-24	00:00:00	OUT
5045	E	11-Sep-24	08:19:25	IN
5045	E	11-Sep-24	08:18:14	IN

Desired Result

ID	NAMEID	DATE	TIME	INOUT
5011	D	30-Apr-24	08:00:00	IN
5011	D	30-Apr-24	08:00:00	IN
5011	D	30-Apr-24	17:00:00	OUT
5045	E	12-Jul-24	17:01:01	IN
5045	E	12-Jul-24	08:11:38	IN
5045	E	12-Jul-24	08:11:36	IN
5045	E	11-Sep-24	08:19:25	IN
5045	E	11-Sep-24	08:18:14	IN

Solution

You can exclude those directly, not based in count:

SELECT a.ID, MASTERID.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID ON a.ID = MASTERID.ID
WHERE ((SELECT COUNT(*)
       FROM ABSEN AS a2
       WHERE a.ID = a2.ID 
         and a.DATE = a2.DATE and a.INOUT = a2.INOUT
         and IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
       ) > 1 OR  (SELECT COUNT(*)
       FROM ABSEN AS a2
       WHERE a.ID = a2.ID 
         and a.DATE = a2.DATE
         and IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
       ) > 2 )
AND IIF(a.OPTION IS NULL, '', a.OPTION) <> 'NO'