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

Question

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

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

To clarify: you want (5009, 29-Apr-2024) (three rows) to be excluded fro your query because one or more of them has OPTION='NO'? If yes, just change your GROUP by in EXISTS subquery to 'group by a.ID, a.DATE' (remove OPTION and use commas to separate columns to group by). — tinazmu
– tinazmu, Commented Aug 29, 2024 at 1:55
@tinazmu , you want (5009, 29-Apr-2024) (three rows) to be excluded from your query because one or more of them has OPTION='NO'? If there is a 'NO' option then it is not counted as a record that is more than 2 as an example 5009, 29-Apr-2024 . Okay I will try — dlaksmi
– dlaksmi, Commented Aug 29, 2024 at 2:27
@tinazmu , I've tried it but for (5009, 29-Apr-2024) appear 2 records but what I want (5009, 29-Apr-2024) does not appear anymore because it is not more than 2 records with the help column OPTION — dlaksmi
– dlaksmi, Commented Aug 29, 2024 at 2:34
@tinazmu , Is there any other solution?, please guide me from you — dlaksmi
– dlaksmi, Commented Aug 29, 2024 at 4:58
Why did you use GROUP BY ID & [Date] ; you should use comma , to separate columns in the GROUP BY. Your requirement around OPTION is unclear to me. ` it is not more than 2 records with the help column OPTION`; explain what this means a bit further (how do you use the OPTION column; are the blank ones actually NULL or blank; how do you treat N, blank, null, or any other value? — tinazmu
– tinazmu, Commented Aug 29, 2024 at 6:08

tinazmu · Accepted Answer · 2024-08-30 00:11:15Z

1

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;

edited Aug 30, 2024 at 0:11

answered Aug 29, 2024 at 13:15

tinazmu

5,5442 gold badges12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

dlaksmi Over a year ago

Your answer is perfect and sorry I was late replying

dlaksmi Over a year ago

I have a new post and I use your answer in this post so I hope you can help. link

June7 · Accepted Answer · 2024-08-27 08:45:33Z

1

Use 2 nested queries and IN() function:

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 a.ID IN(SELECT ID FROM ABSEN GROUP BY ID HAVING Count(*)>2)
ORDER BY a.ID, a.DATE, a.INOUT;

Based on revised sample data:

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 a.ID & [Date] IN(SELECT ID & [Date] FROM ABSEN GROUP BY ID & [Date] HAVING Count(*)>2)
ORDER BY a.ID, a.DATE, a.INOUT;

edited Aug 27, 2024 at 8:45

answered Aug 27, 2024 at 6:40

June7

21.7k8 gold badges28 silver badges36 bronze badges

8 Comments

dlaksmi Over a year ago

Thanks for your answer but I'm trying to add a record for ID 5011 With 28-Aug-24 with each in out But the record appeared after I updated your answer

dlaksmi Over a year ago

I'm updating in the post Table Absen for testing answer from @june7 and Result from answer from @june7

June7 Over a year ago

See revised answer.

dlaksmi Over a year ago

Thanks for your answer . I'm analyzing it in the actual MS access So give me time to analyze and mark your answers, but not all of them appear double. Example m.NAMEID='A' If I use the where condition where I put in your SQL answer code Is it before this code a.ID NOT IN(select ID from ABSEN WHERE Option="NO") . Is it like thisWHERE m.NAMEID='A' and a.ID NOT IN(select ID from ABSEN WHERE Option="NO")

June7 Over a year ago

Sorry, I don't understand your comment. The query I posted produces the desired output shown in your question.

|

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related