How select query from record count double in MS Access

Question

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

I want to keep it as a single query. What are the possibilities of this being applied? Or another method?

Please guide me, 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
5010	29-Apr-24	08:00	IN

Table MASTERID

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

I used this SQL code which did not produce anything:

SELECT 
    ABSEN.ID, MASTERID.NAMEID AS NAMEID, ABSEN.DATE, 
    MASTERID.POSITIONID AS POSITIONID, ABSEN.TIME, ABSEN.INOUT, 
    COUNT(*) AS Expr1
FROM 
    ABSEN 
INNER JOIN 
    MASTERID ON ABSEN.ID = MASTERID.ID
GROUP BY 
    ABSEN.ID, MASTERID.NAMEID, ABSEN.DATE, MASTERID.POSITIONID, 
    ABSEN.TIME, ABSEN.INOUT, ABSEN.DATE
HAVING 
    (((COUNT(*)) > 1));

Desired output

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

KeithL · Accepted Answer · 2024-08-20 12:55:41Z

2

Tricky how you want a single query... Watch out what you ask for because it is kind of impossible to do that without subqueries.

By the way, I am guessing you want the IDs where the INOUT has an OUT. And then order it by ID and TIME.

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 and  a2.INOUT='OUT')
order by ID, DATE, TIME

sorry to do this to you but I am certain that works in SQL Server, this is a 100% solution.

where a.ID = (select a2.ID from ABSEN a2 where 
          a.ID=a2.ID and a2.INOUT='OUT')

edited Aug 20, 2024 at 12:55

answered Aug 20, 2024 at 4:46

KeithL

5,6193 gold badges23 silver badges30 bronze badges

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

10 Comments

Abdul Alim Shakir Over a year ago

Order by Date is also required. Nice answer. Upvoted.

dlaksmi Over a year ago

@KeithL , Thank you for your perfect answer. I make your answer look like this where exists(select 1 from ABSEN a2 where a.ID=a2.ID) order by a.ID,a.DATE result is same.

dlaksmi Over a year ago

@KeithL , May I ask if the SQL code from your answer if I want to select a query record that is more than 2 records with order by a.ID,a.DATE How can I do by adapting the SQL code of your answer?. Please Guide me

June7 Over a year ago

More than 2 of an ID regardless of date and time and inout? Can each ID be associated with multiple dates?

June7 Over a year ago

That "100% solution" is not working in Access.

|

Collectives™ on Stack Overflow

How select query from record count double in MS Access

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related