How to separate clock in and out from 1 same column in PHP

Question

I have a little problem with the attendance machine in my office. I want to separate the time in and out which are in 1 column. I've tried so many queries in two weeks and i can't get what i want. I want to separate the values ​​from the PunchDatetime column into 2 new columns namely in and out, after that i want to move this datas to MySQL. The database I use is Microsoft Access and the language I use is PHP. This is my ms access database:

My MS Access database:

And my expectations, I would have an output like this:

My expectations:

Can anybody help me please? Thanks before and sorry for my bad english

Answer 1

You can use MIN/MAX subqueries to get the information that you want:

SELECT DISTINCT T.CardNo, Format(T.PunchDateTime,"dd/mm/yyyy") AS PunchDate,
(SELECT Format(MIN(S.PunchDateTime),"hh:nn") FROM Tran_MachineRawPunch AS S WHERE S.CardNo=T.CardNo AND Format(S.PunchDateTime,"dd-mm-yyyy")=Format(T.PunchDateTime,"dd-mm-yyyy")) AS PunchIn,
(SELECT Format(MAX(S.PunchDateTime),"hh:nn") FROM Tran_MachineRawPunch AS S WHERE S.CardNo=T.CardNo AND Format(S.PunchDateTime,"dd-mm-yyyy")=Format(T.PunchDateTime,"dd-mm-yyyy")) AS PunchOut
FROM Tran_MachineRawPunch AS T

Note that for any day where there is only one entry (such as 02 Feb and 06 Feb), the two times will be identical - you could exclude these by either converting to a GROUP BY query and seeing if the count of entries is equal to 2, or else add a where clause to exclude those entries where the two times match.

Regards,

Answer 2

This will do for the days with two punches:

Select
    [Employee ID],
    Int([PunchDatetime]) As [Date],
    Min([PunchDatetime]) As [In],
    Max([PunchDatetime]) As [Out]
From
    Tran_MachineRawPunch
Group By
    [Employee ID],
    Int([PunchDatetime])

It will return the same time for In and Out for those dates with only one punch.

The In and Out values must be formatted for time only.