CODEWITHSUNDEEP
sqlms-accessgroup-byhaving
dlaksmi
dlaksmi

Reputation: 369

How select query from record count double in MS Access

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

Upvotes: 1

Views: 60

Answers (1)

KeithL
KeithL

Reputation: 5594

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

Upvotes: 2

Related Questions