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,418
    Provided Answers: 7
    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 22:49. Reason: spelling
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  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
  •