Insert date/time on last modified record in Access 2013

1
votes

I built a simple time clock database. The table has four columns:

  • ID (autonumber)
  • EmployeeID
  • TimeIn (Date/Time)
  • TimeOut (Date/Time).

An employee sees only a form with two buttons, Clock In and Clock Out. Each button has an On Click event to add a new record to the table with the fOSUserName and Now() under TimeIn or TimeOut, depending on which button is clicked.

All of this works great, however, I need a report to show the number of hours worked per day. In order to get a report with accurate DateDiff(), the TimeIn and TimeOut data has to be on the same row. I've tried LastModified, MovePrevious/MoveNext, and IfNull and I can't get TimeOut on the same row without manually moving it.

2
ms-accessvbams-access-2013datediff

2 Answers

0
votes

Not perfect, but you could start with something like this: 

update clock 
set timeOut =  Now()
where employeeId = fOsUsername()
and id in (select top 1 id from clock where employeeId = fOsUsername() 
                  and timeOut is null and timeIn > Date()-1
                  order by id desc
          )
0
votes

You could do like this:

Select
    EmployeeID,
    Sum(TimeIn) As ClockIn,
    Sum(TimeOut) As ClockOut
From
    YourClockTable
Group By
    EmployeeID,
    DateValue(Nz(TimeIn, TimeOut))