How select query from record count more than 2 in MS Access

Question

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

Please Guide me

I want to keep it as a single query.

Thanks

Table Absen

ID	DATE	TIME	INOUT
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
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 a INNER JOIN MASTERID ON a.ID = MASTERID.ID
where exists(select 1 from ABSEN a2 where a.ID=a2.ID)
order by a.ID, a.DATE

Result From code

ID	NAMEID	DATE	TIME	INOUT
5008	A	28-Apr-24	08:00	IN
5008	A	28-Apr-24	17:00	OUT
5009	B	29-Apr-24	08:00	IN
5009	B	29-Apr-24	17:00	OUT
5009	B	29-Apr-24	17:00	OUT
5010	C	28-Apr-24	08:00	IN
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 output

ID	NAMEID	DATE	TIME	INOUT
5009	B	29-Apr-24	08:00	IN
5009	B	29-Apr-24	17:00	OUT
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

User D has both IN and OUT time in the same date. What is business logic to add three records of User D in the desired output? — Abdul Alim Shakir
– Abdul Alim Shakir, Commented Aug 20, 2024 at 11:25

jarlh · Accepted Answer · 2024-08-20 12:13:01Z

1

Try a GROUP BY subquery in the WHERE clause to get the ID's having at least one count per day > 2.

select a.ID, MASTERID.NAMEID, a.DATE, a.TIME, a.INOUT
from ABSEN a INNER JOIN MASTERID ON a.ID = MASTERID.ID
where a.id IN (select ID
               from ABSEN
               group by ID, date
               having count(*) > 2)
order by a.ID, a.DATE

answered Aug 20, 2024 at 12:13

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

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

Collectives™ on Stack Overflow

How select query from record count more than 2 in MS Access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related