Punch Card

[sonnierock]

I got this problem, I have a table full of records like these

EMP ID DATE TIME
===== ==== ====
A0001 011002 2200 (clock in time)
A0001 021002 0700 (clock out time)

==================

I would like to change them to this,

EMP ID DATE IN OUT
===== ==== ==== ====
A0001 011002 2200 0700 (same line)

=========================

Thanks for helping

 

[raskew]

First, an assumption that your date and time fields are really Text fields.

Next, you need to change the names of the Date and Time fields, since
both are reserved words which will create confusion for the system.
Try Datex and Timex.

In order to take advantage of Access' date & time related functions,
the text representations need to be converted to actual date/time.

Recommend you check out this article which will provide insight as to
how dates / times are stored by Access.

support.microsoft.com/support/kb/articles/Q130/5/14.ASP

As you'll see, a Date/Time data type is stored as a double-precision,
floating-point number (up to 15 decimal places).
- The integer portion of the double-precision number representing the date
- The decimal portion representing the time.

Assuming your table is named tblEmpTimesOld and the fields
are Datex and Timex, here's a query you can run which will show your dates /
times converted to a date/time data type, and also how it's stored by Access.

SELECT tblEmpTimesOld.EmpID, tblEmpTimesOld.Datex, tblEmpTimesOld.Timex, DateValue(Mid([datex],3,2) & "/" & Left([datex],2) & "/" & Right([datex],2))+TimeValue(Left([timex],2) & ":" & Right([timex],2)) AS realDT, CDbl([realDT]) AS stored
FROM tblEmpTimesOld
ORDER BY tblEmpTimesOld.EmpID, DateValue(Mid([datex],3,2) & "/" & Left([datex],2) & "/" & Right([datex],2))+TimeValue(Left([timex],2) & ":" & Right([timex],2));

To get your start and end date/times (can see no point in
separating the date and the time in individual fields) into one
record, create a new table (tblEmpTimesNew) with fields:
EmpID: Text
StartTime: date/time (format as general date)
EndTime: date/time (format as general date)

PROVIDED that your records are matched pairs (a start
date/time record and a corresponding end date/time record),
the following code, placed in a new module should fill your new
table. Call it from the debug window with: ? TimeConv()

Function TimeConv()
Dim db As DATABASE
Dim rs1 As Recordset, rs2 As Recordset
Dim idHold As String, strSQL As String
Dim i As Integer, n As Integer

Set db = CurrentDb

strSQL = "SELECT tblEmpTimesOld.EmpID, tblEmpTimesOld.Datex, tblEmpTimesOld.Timex, DateValue(Mid([datex],3,2) & '/' & Left([datex],2) & '/' & Right([datex],2))+TimeValue(Left([timex],2) & ':' & Right([timex],2)) AS realDT" _
    & " FROM tblEmpTimesOld" _
    & " ORDER BY tblEmpTimesOld.EmpID, DateValue(Mid([datex],3,2) & '/' & Left([datex],2) & '/' & Right([datex],2))+TimeValue(Left([timex],2) & ':' & Right([timex],2));"

Set rs1 = db.OpenRecordset(strSQL)
Set rs2 = db.OpenRecordset("tblEmpTimesNew")

'ensure that rs1 contains records
rs1.MoveLast
i = rs1.RecordCount
rs1.MoveFirst
If i > 0 Then
   Do While Not rs1.EOF
      idHold = rs1!EmpID
      Do While rs1!EmpID = idHold
        With rs2
        .AddNew
        !EmpID = idHold
        For n = 1 To 2
           If n = 1 Then
              !starttime = rs1!realDT
           Else
              !endtime = rs1!realDT
           End If
           rs1.MoveNext
           If rs1.EOF Then Exit For
        Next n
        .Update
        End With
        If rs1.EOF Then Exit Do
      Loop
   Loop
End If
rs1.Close
rs2.Close
db.Close
Set db = Nothing
End Function

 

[sonnierock]

Is there any simple query to do it, as I already converted both date and time field into date/time format.

Thanks for helping but I am really a novice.

 

[Jon K]

Assuming DATE, TIME are date/time fields in table tblPunchCard, try these two queries (type/paste in the SQL View of each new query):

qryOne:-
SELECT [EMP ID], [DATE], [TIME],
(Select count(*) from [tblPunchCard] where [EMP ID] = a.[EMP ID] and [Date]+[Time] <= a.[Date]+a.[Time]) AS Num,
iif(Num mod 2 =1, "In" ,"Out") AS InOut,
iif(InOut="In", Num, Num-1) AS Pair
FROM [tblPunchCard] AS a;


qryTwo:-
SELECT [EMP ID], Min([Date]) AS [Clock-In Date],
format(Min([Date]+[Time]), 'Short time') AS [Clock-in Time],
iif(Max([Date]+[Time]) <> Min([Date]+[Time]), Max([Date]), Null) AS [Clock-out Date],
iif(Max([Date]+[Time])<>Min([Date]+[Time]), format(Max([Date]+[Time]) ,'Short Time'), Null) AS [Clock-out Time]
FROM qryOne
GROUP BY [EMP ID], Pair;


Run the second query.

(As clock-in date and clock-out date are different in the sample data, I have included [Clock-out Date] in the query result. You can delete it if you want to.)

 

[sonnierock]

Thanks alot guys

It works, it works though I am still trying to understand what Jon K wrote. Thanks Jon K

Thanks also to Raskew.



How you guys did it?

 

[mbertuol]

Hello, I have a similar situation. However I have a table to each employee. The table is like:

IO DateIO TimeIO
IN 11/10/2004 09:02:00
OUT 11/10/2004 14:01:00
IN 11/10/2004 14:23:00
OUT 11/10/2004 20:27:00
IN 13/10/2004 08:32:00
OUT 13/10/2004 13:50:00
IN 13/10/2004 14:42:00
OUT 13/10/2004 16:23:00

As you can see I have 4 rows to calculate the time for a single day.
Any idea on how I could manage this?

Thanks in advance for any help.

Mauro