Thread: How to get total value from this?

1. Registered User
Join Date
Nov 2011
Posts
2

Unanswered: How to get total value from this?

Hi All,

I have one table in access and it has a column which captures employees in and out info. It is like this:

7/28/2011 9:38 abc 111-03834
7/28/2011 11:36 abc 111-03834
7/28/2011 12:54 abc 111-03834
7/28/2011 14:04 abc 111-03834
7/28/2011 15:08 abc 111-03834

I want to get the difference b/w 1st value and the last value in Date time column, so that we can know how many hours he worked, don't have to worry about the middle values, how do I do this ?

Thanks for any tips and suggestions.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
step 1 Play around with GroupBY and the First(min) last(max)

so starttime Query

SELECT ClockINout.Cname, Format([tdate],"dd/mm/yyy") AS Mydate, First(ClockINout.tdate) AS Stime
FROM ClockINout
GROUP BY ClockINout.Cname, Format([tdate],"dd/mm/yyy");

So Endtime Query

SELECT ClockINout.Cname, Format([tdate],"dd/mm/yyy") AS Mydate, Last(ClockINout.tdate) AS Etime
FROM ClockINout
GROUP BY ClockINout.Cname, Format([tdate],"dd/mm/yyy");

now join the to Querys STEP 1 ( check Does it Work)

SELECT Starttime.Cname, Starttime.Mydate, Starttime.STIME, EndTIme.Etime, DateDiff("n",[Stime],[Etime]) AS MInsWorked
FROM Starttime INNER JOIN EndTIme ON Starttime.Cname = EndTIme.Cname;

I allways work in Mins because math is easer

60min + 30min = 90min

Heres A Function I wrote that convert mins to time Format

Code:
```Function MintoHrs(num)
If num < 60 Then
MintoHrs = "00:" & Format(num, "00")
Else
hh = Int(num / 60)
temp = (hh * 60)
MintoHrs = Format(hh, "00") & ":" & Format((num - temp), "00")
End If
End Function```
MintoHrs(330) would return 05:30

MintoHrs(3300) would return 55:00

ok

Copy the above code in a module save it

then Change the Above Query

in the query just HH:MintoHrs(fieldname) HH will show the hows

SELECT Starttime.Cname, Starttime.Mydate, Starttime.STIME, EndTIme.Etime, DateDiff("n",[Stime],[Etime]) AS MInsWorked, mintohrs([MInsWorked]) AS HH
FROM Starttime INNER JOIN EndTIme ON Starttime.Cname = EndTIme.Cname
GROUP BY Starttime.Cname, Starttime.Mydate, Starttime.STIME, EndTIme.Etime, DateDiff("n",[Stime],[Etime]);

and with the MintoHrs function it will also work in reports by suming up the total mins then =MintoHrs(sumOFMIns)

I used it for a time Tacking System I wrote user log everything in mins and the Managers get a month end report Display by weekend total Hours Booked

but the Managers get piss off with me as it report to 2294:06 they say they don't need to know the 6min LOL

have upload the Play File I created
Last edited by myle; 11-09-11 at 22:49. Reason: spelling

3. Registered User
Join Date
Nov 2011
Posts
2

Thanks

Thank you so much, it works fine..

Regards

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•