Results 1 to 3 of 3
  1. #1
    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:

    Date and Time Name CardReader
    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.

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    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
    Attached Files Attached Files
    Last edited by myle; 11-09-11 at 21:49. Reason: spelling
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    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
  •