Attendence In/Out Arrange (1 Viewer)

indrajeetpramalik · Aug 14, 2019

theDBguy · Aug 14, 2019

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 · Aug 14, 2019

theDBguy said:
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 · Aug 14, 2019

Does this achieve what you want?

Code:

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 · Aug 14, 2019

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 · Aug 14, 2019

isladogs said:

Does this achieve what you want?

Code:

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 · Aug 14, 2019

ypma said:
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 · Aug 15, 2019

Here is your query.

indrajeetpramalik · Aug 15, 2019

arnelgp said:
Here is your query.

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

arnelgp · Aug 15, 2019

you're welcome!

Attendence In/Out Arrange (1 Viewer)

indrajeetpramalik

New member

Attachments

theDBguy

I’m here to help

indrajeetpramalik

New member

isladogs

MVP / VIP

ypma

Registered User.

Attachments

indrajeetpramalik

New member

indrajeetpramalik

New member

arnelgp

..forever waiting... waiting for jellybean!

Attachments

indrajeetpramalik

New member

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread