Search code examples
sqlms-accesssql-order-byexists

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

I'm trying to select query from row count more than 2 based on ID and DATE and OPTION in MS Access.

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

Is there something wrong with my SQL code ?

so my post is different from the link below

link

Please Guide me

Table Absen

ID DATE TIME INOUT OPTION
5008 28-Apr-24 08:00 IN
5008 28-Apr-24 17:00 OUT
5009 29-Apr-24 08:00 IN
5009 29-Apr-24 17:00 OUT
5009 29-Apr-24 17:00 OUT NO
5010 28-Apr-24 08:00 IN
5011 30-Apr-24 08:00 IN
5011 30-Apr-24 08:00 IN
5011 30-Apr-24 17:00 OUT

Table MASTERID

ID NAMEID POSITIONID
5008 A STAFF
5009 B STAFF
5010 C STAFF
5011 D STAFF

I used this SQL code result like this:

SELECT a.ID, MASTERID.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID ON a.ID = MASTERID.ID
WHERE a.option is null and exists(select a.ID from ABSEN a2 where a.ID=a2.ID and a.DATE=a2.DATE group by a.ID and a.DATE and a.option having count(*) > 2)
ORDER BY a.ID, a.DATE, a.INOUT;

Result From code

ID NAMEID DATE TIME INOUT
5009 B 29-Apr-24 08:00 IN
5009 B 29-Apr-24 17:00 OUT
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 17:00 OUT

Desired result

ID NAMEID DATE TIME INOUT
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 17:00 OUT

Table Absen for testing answer from @june7

ID DATE TIME INOUT OPTION
5008 28-Apr-24 08:00 IN
5008 28-Apr-24 17:00 OUT
5009 29-Apr-24 08:00 IN
5009 29-Apr-24 17:00 OUT
5009 29-Apr-24 17:00 OUT NO
5010 28-Apr-24 08:00 IN
5011 30-Apr-24 08:00 IN
5011 30-Apr-24 08:00 IN
5011 30-Apr-24 17:00 OUT
5011 28-Aug-24 08:00 IN
5011 28-Aug-24 17:00 OUT
5011 29-Aug-24 08:00 IN
5011 29-Aug-24 17:00 OUT

Result from answer from @june7

ID NAMEID DATE TIME INOUT
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 08:00 IN
5011 D 30-Apr-24 17:00 OUT
5011 D 28-Aug-24 08:00 IN
5011 D 28-Aug-24 17:00 OUT
5011 D 29-Aug-24 08:00 IN
5011 D 29-Aug-24 17:00 OUT

Table Absen for testing answer update from @june7 28-08-2024

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
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

Table MASTERID

ID NAMEID POSITIONID
5045 M STAFF

with Code Answer update

SELECT a.ID, m.NAMEID, a.DATE, a.TIME, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID AS m ON a.ID = m.ID
WHERE a.ID NOT IN(select ID from ABSEN WHERE Option="NO")
AND m.POSITIONID ='STAFF' and a.ID & [Date] IN(SELECT ID & [Date] FROM ABSEN GROUP BY ID & [Date] HAVING Count(*)>2)
ORDER BY a.ID, a.DATE, a.INOUT;

Result Answer update (no record)

ID NAMEID DATE TIME INOUT

with old code posting

SELECT a.ID, MASTERID.NAMEID, a.Date, a.Time, a.INOUT
FROM ABSEN AS a INNER JOIN MASTERID ON a.ID = MASTERID.ID
WHERE (((MASTERID.POSITIONID)='STAFF') AND ((a.OPTION) Is Null) AND ((Exists (select a.ID from ABSEN a2 where a.ID=a2.ID and a.DATE=a2.DATE group by a.ID having count(*) > 2))<>False))
ORDER BY a.ID, a.Date, a.INOUT;

Result old code posting

ID NAMEID DATE TIME INOUT
5045 M 12-Jul-24 17:01:01 IN
5045 M 12-Jul-24 08:11:38 IN
5045 M 12-Jul-24 08:11:36 IN
5045 M 12-Jul-24 0 OUT
Solution

  • You want to list rows if ID&DATE appear more than twice (not counting rows with OPTION='N'):

    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 IIF(a2.OPTION IS NULL, '', a2.OPTION) <> 'NO'
       ) > 2
ORDER BY a.ID, a.DATE, a.INOUT;