SELECT DISTINCTROW Log.Date, Log.EnNo, Min(Log.Time) AS LogInTime, Min(Log_1.Time) AS LogoutTime
FROM Log INNER JOIN Log AS Log_1 ON (Log.EnNo = Log_1.EnNo) AND (Log.Date = Log_1.Date)
GROUP BY Log.Date, Log.EnNo, Log.Direction, Log_1.Direction
HAVING (((Log.Direction)='IN') AND ((Log_1.Direction)='OUT'))
ORDER BY Log.EnNo, Min(Log.Time), Min(Log_1.Time);
Hello Malik, as a user like you and not a programmer i would create two queries. to achieve your aim.Open query 2 to see my idea . Other members might be able to give you a better solution.
SELECT DISTINCTROW Log.Date, Log.EnNo, Min(Log.Time) AS LogInTime, Min(Log_1.Time) AS LogoutTime
FROM Log INNER JOIN Log AS Log_1 ON (Log.EnNo = Log_1.EnNo) AND (Log.Date = Log_1.Date)
GROUP BY Log.Date, Log.EnNo, Log.Direction, Log_1.Direction
HAVING (((Log.Direction)='IN') AND ((Log_1.Direction)='OUT'))
ORDER BY Log.EnNo, Min(Log.Time), Min(Log_1.Time);
In Data Table, for Date 12-07-2019 & EnNo(Punch ID) 1 i have 31 times In & Out record like:
Date EnNo Time Direction 12-07-2019 1 2.22.00 PM IN
12-07-2019 1 2.29.19 PM OUT
12-07-2019 1 2.30.25 PM IN
12-07-2019 1 2.30.29 PM OUT
12-07-2019 1 2.30.33 PM IN
12-07-2019 1 2.30.37 PM OUT
12-07-2019 1 2.30.40 PM IN
12-07-2019 1 2.30.42 PM OUT
12-07-2019 1 2.30.47 PM IN
12-07-2019 1 2.30.55 PM OUT
12-07-2019 1 2.30.59 PM IN
12-07-2019 1 2.31.53 PM OUT
12-07-2019 1 2.31.59 PM IN
12-07-2019 1 2.32.06 PM OUT
12-07-2019 1 2.32.07 PM IN
12-07-2019 1 2.32.09 PM OUT
12-07-2019 1 2.32.11 PM IN
12-07-2019 1 2.32.15 PM OUT
12-07-2019 1 2.32.30 PM IN
12-07-2019 1 2.32.32 PM OUT
12-07-2019 1 2.32.34 PM IN
12-07-2019 1 2.32.36 PM OUT
12-07-2019 1 2.32.38 PM IN
12-07-2019 1 2.32.44 PM OUT
12-07-2019 1 2.32.46 PM IN
12-07-2019 1 2.34.22 PM OUT
12-07-2019 1 2.34.25 PM IN
12-07-2019 1 2.34.27 PM OUT
12-07-2019 1 2.34.28 PM IN
12-07-2019 1 2.34.31 PM OUT
12-07-2019 1 2.34.33 PM IN
But with your query i get only 1 record for the date 12-07-19 like below
Hello Malik, as a user like you and not a programmer i would create two queries. to achieve your aim.Open query 2 to see my idea . Other members might be able to give you a better solution.