Build recordset with criteria and by date

[tjham]

Here goes~
I am trying to build a table containing
EmpNo
Name
TimeIn
TimeOut
TimeStamp
Date

The employee clocks in, a record is created and has a timestamp.
The employee clocks out, a record is created and has a timestamp.

They can clock in and out several times during the day. The main table will be where "every" employee's In and Out will be stored.

I'm creating a Time Sheet for the them to view the In and Out, but I need to get the In and Out on the same record according to the date.

So far, I've made a query to create a EmpLog table according to who is logged in. From this, I need to get the records on one record according to date.

The Code is:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Option Compare Database
Public TimeLOGIn As ADODB.Recordset
Public EmpFinalTimeLog As ADODB.Recordset

Private Sub Command2_Click()
'------------------------------------------------------------

DoCmd.SetWarnings False
'------------------------------------------------------------
' Run Query to clear out table
'------------------------------------------------------------
DoCmd.OpenQuery "qryClearEmpLog", acNormal, acEdit
DoCmd.OpenQuery "qryClrTimeLogOUTTable", acNormal, acEdit
DoCmd.OpenQuery "MakeTimeLogEmp", acNormal, acEdit

'------------------------------------------------------------
' Open TimeLOGIn Table
'------------------------------------------------------------

Set TimeLOGIn = New ADODB.Recordset


TimeLOGIn.Open "Timelogin", CurrentProject.Connection, adOpenKeyset, adLockOptimistic



'------------------------------------------------------------
' READ First Record of the TimeLOGIn Table
'------------------------------------------------------------
TimeLOGIn.MoveFirst
'------------------------------------------------------------
' READ TimeLOGIn Table is EOF
'------------------------------------------------------------
Do Until TimeLOGIn.EOF

'------------------------------------------------------------
' Open EmpFinalTimeLog Table
'------------------------------------------------------------
Set EmpFinalTimeLog = New ADODB.Recordset
EmpFinalTimeLog.Open "EmpFinalTimeLog", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
TimeLOGIn.MoveNext
'------------------------------------------------------------
' This will add a new record
'------------------------------------------------------------
EmpFinalTimeLog.AddNew
'------------------------------------------------------------
' If Timeout field is = to 12:00 AM
'------------------------------------------------------------
If TimeLOGIn.Fields("TimeOut&quot = #12:00:00 AM# Then
'------------------------------------------------------------
' Move fields from Login record to fields in the Logout record
'------------------------------------------------------------
EmpFinalTimeLog.Fields("TimeStamp&quot = TimeLOGIn.Fields("TimeStamp&quot
EmpFinalTimeLog.Fields("EmpNo&quot = TimeLOGIn.Fields("Empno&quot
EmpFinalTimeLog.Fields("Name&quot = TimeLOGIn.Fields("Name&quot
EmpFinalTimeLog.Fields("TimeIn&quot = TimeLOGIn.Fields("TimeIn&quot
EmpFinalTimeLog.Fields("Date&quot = TimeLOGIn.Fields("Date&quot
'------------------------------------------------------------
' Read next record in the TimeLOGIn Table
'------------------------------------------------------------
TimeLOGIn.MoveNext
'------------------------------------------------------------
' if TimeLOGIn is EOF then update EmpFinalTimeLog table and goto end of program
'------------------------------------------------------------
If TimeLOGIn.EOF Then
EmpFinalTimeLog.Update
GoTo 50
Else

End If


'------------------------------------------------------------
' If not EOF and TimeOut field in LOGIn record is not equal
' to 12:00 AM then update LOGOut field TimeOut with TimeOut
' field in the LOGIn record
' then update the LOGOut table
'------------------------------------------------------------
If TimeLOGIn.Fields(&quot;TimeOut&quot <> #12:00:00 AM# Then
EmpFinalTimeLog.Fields(&quot;TimeOut&quot = TimeLOGIn.Fields(&quot;TimeOut&quot
EmpFinalTimeLog.Update
End If
'------------------------------------------------------------
' Read next record in the TimeLOGIn Table
'------------------------------------------------------------
TimeLOGIn.MoveNext

If TimeLOGIn.EOF Then GoTo 50
End If

Loop
50
TimeLOGIn.Close

MsgBox (&quot;File Has Been Updated&quot

End Sub

Private Sub OK_Click()
On Error GoTo Err_OK_Click


DoCmd.Close

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox err.Description
Resume Exit_OK_Click

End Sub

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
This is ran from a button on a form, for now.
This sample is from the table it gets the info from:
TimeStamp EmpNo Name TimeIN TimeOut Date

2/3/2004 5:23:20 PM 1184 Tanya 5:30:00 AM 12:00:00 AM 2/3/2004
2/3/2004 5:23:23 PM 1184 Tanya 12:00:00 AM 5:45:00 PM 2/3/2004
2/3/2004 5:23:26 PM 1184 Tanya 6:30:00 PM 2:00:00 AM 2/3/2004
2/3/2004 5:23:29 PM 1184 Tanya 12:00:00 AM 6:45:00 PM 2/3/2004
2/3/2004 5:23:31 PM 1184 Tanya 8:30:00 PM 12:00:00 AM 2/3/2004
2/3/2004 5:23:33 PM 1184 Tanya 12:00:00 AM 10:30:00 PM 2/3/2004
2/4/2004 5:37:58 PM 1184 Tanya 5:30:00 PM 12:00:00 AM 02/4/2004
2/4/2004 5:41:49 PM 1184 Tanya 12:00:00 AM 5:45:00 PM 2/4/2004
2/4/2004 6:14:17 PM 1184 Tanya 6:15:00 PM 12:00:00 AM 2/4/2004

My results:

TimeStamp EmpNo Name TimeIN TimeOut Date
2/4/2004 5:37:58 PM 1184 Tanya 5:30:00 PM 10:30:00 PM 2/4/2004
2/3/2004 5:23:26 PM 1184 Tanya 6:30:00 PM 5:45:00 PM 2/3/2004

I'd appreciate any help!

 

[dhookom]

It would help if you would have entered exactly what you expected your output to look like. I assume you want to combine all the in and out for a single date into one expression. If this is correct, read faq701-4233. There is a sample mdb you can download at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tjham]

Thank you for your help...

I did forget to state what I wanted as an end result, sorry~

What I need is the In and Out for one day to be on one row. If the employee has one in and one out I need to calculate the total time for each in and out.
In at 8a.m.
Out at 12p.m.
Will be 4 hrs. total. The report needs to calculate the time or I can have the qry do it.

The final report layout is as follows:

Employee# Name
Date1 In: Out:
8:00a 12:00p
1:00p 5:00p Total hours: 8.00 hours
Date2 8:15a 10:30p
11:00p 12:00p
1:00p 5:00p Total hours: 7.75 hours
The In and Out can be multiple times a day. You can't have an OUT without being IN. You can't have an IN if there wasn't an OUT, unless its a new day. The main thing is I need the IN to have the OUT according to the TimeStamp, by date.

I'll check out the links you gave though...I appreciate your help!

 

[dhookom]

This just a simple report that is grouped by
Employee Group Header and Footer
[Date] Group Header and Footer
[TimeIn] No headers or footers
You can get the number of hours
=DateDiff(&quot;n&quot;,[TimeOut],[TimeIn])/60
Summing these in a group footer uses:
=Sum(DateDiff(&quot;n&quot;,[TimeOut],[TimeIn])/60)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tjham]

I appreciate your help!

I can't believe that I've been chasing my tail. When you gave the last thread...I'd already had that...

I was thinking that I had to manipulate the table to get a true hour calculation...

After your last thread I went back to the original table, ran the original report and WALA

I think what threw me off was that the original report wasn't calculating the hours correctly if the employee had two IN or OUT entries in a row...BUT,
this turns out to be good, because it will draw their attention to see that they missed a clock in or out.

Well, Thank you so very much...sometimes I have to take the scenic route!