Time Clock like query 2

[mobbarley]

Hi guys, trying to make a timeclock like query to show the time between first & last record per day, the table is called RTETABLE, has fields DateTime which is: "12/02/03 10:44:25" & Prim which is a numeric identifier for staff.

Here is the query I'm working on, but i'm lost.

SELECT Format(Max(right(trim([DateTime]),8))-Min(right(trim([DateTime),8)]),"hh:nn") AS Elapsed, Format(left(trim([DateTime]),8),"dd-mm-yy") AS [Day], [RTETABLE].[Prim]
FROM [RTETABLE]
GROUP BY Format([Time],"dd-mm-yyyy"), [RTETABLE].[Prim];

Any help greatly appreciated.

Regards,
John R.

 

[scriverb]

I take you want to end up with a single record per staff member that shows the beginning time, endinging time, and the elapsed time. Give this a try:

SELECT RTETABLE.Prime, Min(RTETABLE.DateTime) AS MinOfDateTime, Max(RTETABLE.DateTime) AS MaxOfDateTime, DateDiff("n",Min([RTETABLE]![DateTime]),Max([RTETABLE]![DateTime]))/60 AS ElapsedTime
FROM RTETABLE
GROUP BY RTETABLE.Prime
ORDER BY RTETABLE.Prime;

Let me know if this is what you were looking for.


[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[mobbarley]

Great stuff.. Is there an easy way to group this by day? I.E. show elapsed time for each day, instead of adding up all records?

 

[mobbarley]

GROUP BY left(RTETABLE.DateTime,8), RTETABLE.Prim

seems to do the trick.

 

[scriverb]

Glad to see that worked for you. And, yes, the Group By that you created should give you a seperate row for each day for each staff.

Thanks for the Star.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[robmkimmons]

I've tried the example above and it works great..

However, what can be done to account for more than one punch in the day? i.e. punch out for lunch and then back in.

From what i understand this would significantly change the setup b/c you are using Min/Max right now.

Can it be done?

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[scriverb]

With multiple punch ins and outs each day and you wanting to rollup the combined total you would need to write a function that will loop through the transactions for an employee, calculate the time for each pair of records and sum them for return to the query. This way you would not be doing the calculation directly in the query and could control it with code.

Hope this helps.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JonFer]

Rob - Why don't you use punch-in and punch-out times as separate fields on each record?


John

http://www.interautomateconsulting.com

 

[robmkimmons]

Jonfer - I thought that would violate database normalization rules...??

One possible solution i've been working on through the modification of the above example is to have an "IN/OUT" field. So my table would look as such:

[EmployeeID],[DateTime],[InOut]

My query works as such:

SELECT Left([tblPunch].[DateTime],8) AS [Date], tblPunch.EmployeeID, DateDiff("n",DMin("[DateTime]","tblPunch","[InOut] = 'IN'"),DMin("[DateTime]","tblPunch","[InOut] = 'OUT'"))/60 AS Punch1, DateDiff("n",DMax("[DateTime]","tblPunch","[InOut] = 'IN'"),DMax("[DateTime]","tblPunch","[InOut] = 'OUT'"))/60 AS Punch2
FROM tblPunch
GROUP BY Left([tblPunch].[DateTime],8), tblPunch.EmployeeID;

I thought this would solve it. And it works great... as long as there is only one employee for one day. Also, this only allows two punch-in's and two punch-out's. So, i'm stuck.

I'm sure the loop function that Bob Scriver described would be more elegant. However, My skills aren't honed enough for loops yet.


~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[scriverb]

Here is an untested example of code to use to create your function. Don't know all the particulars of your database table but with what you provided this should get you started. Just copy and paste this into a database module.

Function CalcTime(vEmp_ID) as Double
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim vINOUT as string, vIn as Date, vOut as Date
Dim vWkrTime as Double
Set db = CurrentDB
Set rs = db.OpenRecordset("tblPunch", dbOpenDynaset)
vInOut = "IN"
rs.FindFirst(vEmp_ID)
If Not rs.NoMatch then
   Do
      If vInOut = "IN" then
         vIn = rs("DateTime")
         vInOut = "Out"
      Else
         vOut = rs(DateTime)
         vInOut = "In"
         vWkTime = vWkTime + DateDiff("n",vIn,vOut)/60
      EndIf
      rs.MoveNext
   Loop Until rs.eof or (vEmp_ID <> rs("EmployeeID"))
   CalcTime = vWkTime / 60 
   rs.close
   db.close  
   Exit Function
Else
   CalcTime = 0
End If
rs.close
db.close
End Function

Now in your query for each employee create a column calling this function and pass the EmployeeID as a parameter. This function will return a number with decimals representing the number of hours worked for all the employee records.

Example:

Select A.*, CalcTime(EmployeeID) as WorkHours 
From tblPunch as A 
Order By A.EmployeeID;

Now I didn't include a date search in this function. This could be done by passing a parameter for a date or range of dates and only looking at the recordset records that match that criteria. Safeguards should be made in this function for unmatched IN and Out records so that you don't try to calculate based on only one half of the required records.

Hope this helps you get started.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[JonFer]

I highly suggest having a punch-in and punch-out datetime (or punch-in and duration) on one record. All of the extra SQL you have to do for your current structure would be removed and your queries will be much faster. Sometimes a small violation of normalization rules is the right way to go.



John

http://www.interautomateconsulting.com

 

[scriverb]

I agree with John and could make a case for this record design not really being out of compliance with normalization. You see the transaction that is taking place requires and beginning and an ending date/time. That is the single record that should be considered normalized. The date/time stamp form that you enter the begging and ending info into could be programmed to either start a new record if no hanging open record is found or to bring up and edit the existing record that only has a beginning date/time. But, in the event that you are not able to make that adjustment to your design at this time the above function should get started on your way. You will have to modify the calling query so that only one record for an employee and date will call the function for that date/time.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]