Results 1 to 6 of 6
  1. #1
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67

    Need help calculating time on my report

    I think I made have a design flaw. Trying to calculate time on a report for my employee log in/log out table. This is my table



    staff_log_empl_num text
    staff_log_date date/time
    time_in_time_out text "time in" or "time out"
    staff_log_time date/time

    I may not have designed the table correctly. I'm trying to create a form that calculates the hours worked per employee, per day, per week. A pic of my report is attached. Do I need to redesign the table or can I do something w/ the way it is?

    doc1.pdf

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Your data is normalized but calculating values of sequential records can be difficult. Requires subqueries. For one method, see the example 'Get the value in another record' at http://allenbrowne.com/subquery-01.html

    Your situation is complicated because you want to calculate daily and weekly totals, not just a month cut-off date.

    Is there only one In/Out pair per day? Try doing a query for the Ins and a query for the Outs, in another query join them on the date. This will get the In and Out pairs in a single record and can calculate the difference. The text time values will have to be converted to a date/time value to do a DateDiff calculation.

    SELECT LogDate, LogTime, LogDate & " " & LogTime As LogDateTime WHERE LogInOut = "IN";
    SELECT LogDate, LogTime, LogDate & " " & LogTime As LogDateTime WHERE LogInOut = "OUT";

    Use DatePart to extract a week number that can be used as grouping criteria in summary calcs: DatePart("ww", LogDate)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    Thanks June7 I'll take a look at the article

  4. #4
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    I've decided to change the structure of the table. Lesson learned . I will make the time in and time out all the same record. Thanks for the response.

  5. #5
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    Hallelujah, I found the answer! thanks June7 for putting me on the right path

    Code:
    SELECT  sl1.staff_log_date, sl1.staff_log_empl_num,  (DATEDIFF("s",sl1.staff_log_time, sl2.staff_log_time)/60.00)/60 as  tester, *
    FROM staff_login sl1 INNER JOIN staff_login sl2
    ON sl1.staff_log_empl_num = sl2.staff_log_empl_num
    AND sl1.staff_log_date = sl2.staff_log_date
    WHERE sl1.time_in_time_out = 'IN' and sl2.time_in_time_out = 'OUT';
    I abandoned the idea of restructuring the table. I joined the table to itself then used the datediff function to get the hours

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Glad you figured it out. That is essentially what I suggested but in an all-in-one sql. You seem to catch on fast or you really aren't a newbie. Congratulations!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculating Time
    By jlclark4 in forum Forms
    Replies: 0
    Last Post: 04-25-2011, 09:04 AM
  2. Calculating Elapsed Time
    By jo15765 in forum Forms
    Replies: 8
    Last Post: 04-15-2011, 07:00 PM
  3. Replies: 12
    Last Post: 02-22-2011, 03:39 PM
  4. Calculating Trip Time
    By CSUjr in forum Access
    Replies: 2
    Last Post: 08-06-2010, 05:06 AM
  5. Need help with calculating Time total
    By Monoceros in forum Reports
    Replies: 1
    Last Post: 04-20-2009, 12:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums