Attendence In/Out Arrange

[indrajeetpramalik]

I have Data in "Log" Table as.. Date|EnNo (EmployeeID)|Time (Punch Time)|Direction

Now i want those data in a way... Date|EnNo|InPunch|OutPunch

Mdb file with data attached.

 

[theDBguy]

Hi. Welcome to AWF! This should be possible depending on your data structure. I'll see if I can find some time to download your file later.

 

[indrajeetpramalik]

Hi. Welcome to AWF! This should be possible depending on your data structure. I'll see if I can find some time to download your file later.

Thanks for responding.

 

[isladogs]

Does this achieve what you want?

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

 

[ypma]

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.

Regards Ypma

 

[indrajeetpramalik]

Does this achieve what you want?

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

Tried the Query

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

Date EnNo LogInTime LogoutTime
12-07-2019 1 2.22.00 PM 2.29.19 PM
18-07-2019 2 1.27.20 PM 1.27.26 PM
08-08-2019 3 9.31.59 AM 9.17.34 PM
26-07-2019 3 10.23.43 AM 8.28.16 PM
31-07-2019 3 10.37.57 AM 1.07.36 PM
22-07-2019 3 10.38.25 AM 6.35.23 PM
03-08-2019 3 1.10.28 PM 9.23.03 PM
18-07-2019 3 3.57.14 PM 5.17.54 PM
23-07-2019 3 7.10.56 PM 7.11.48 PM
26-07-2018 3 8.27.12 PM 8.27.32 PM

Any other solution will be appriciated...

 

[indrajeetpramalik]

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.

Regards Ypma

I check bro but unfortunately its not happening. Inlogs are repeating in ur query.
Thanks.

 

[arnelgp]

Here is your query.

 

[indrajeetpramalik]

Here is your query.

Bravo... This is perfectly the right 1... Thanks a lot

 

[arnelgp]

you're welcome!