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 |
6
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'
1