Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Question Unanswered: Subtracting overlapping hours in a timesheet.

    I have created a database where employees can enter their work start time and end time such as start time 1:00 AM and end time 5:00 AM. The program calculates well, however it is required that the system allow multiple entries in one day. The question is, how can I prevent duplicate or overlapping hours?
    How can I make Access count 1:00 AM to 5:00 AM and another entry for 4:00 AM to 7:00 AM and not count the overlapping hour or give a warning that one time range is within another?
    Thank you for your help.

  2. #2
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Re: Subtracting overlapping hours in a timesheet.

    Originally posted by Cook
    I have created a database where employees can enter their work start time and end time such as start time 1:00 AM and end time 5:00 AM. The program calculates well, however it is required that the system allow multiple entries in one day. The question is, how can I prevent duplicate or overlapping hours?
    How can I make Access count 1:00 AM to 5:00 AM and another entry for 4:00 AM to 7:00 AM and not count the overlapping hour or give a warning that one time range is within another?
    Thank you for your help.
    If it's only to prevent overlapping hours, then there's no problem.
    No matter how many times he enters his working hours, the starttime is 1:00AM and the endtime is 7:00AM which means 6 hours.
    On the other hand, if you also have to take care of periods of non-activity between 2 entries (1:00AM-5:00AM and 7:00AM-11:00AM) then you need to check if the second entry falls within the first period.
    If yes, you have overlapping hours and the sum = endtime second entry - starttime first entry.
    If no, you need to make a sum of the two seperate periods.

    Hope this helps.
    If not or if you want more info, just let me know.

  3. #3
    Join Date
    Oct 2003
    Posts
    3

    Question

    I do have multiple entries for one date. How can I make Access check for overlapping hours? Right now I have to inspect all the records for a particular date and id number to see if one range of hours is within another range or overlaps the range. How can I get Access to do this?
    Thank you.

  4. #4
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243
    QUOTE]Originally posted by Cook
    I do have multiple entries for one date. How can I make Access check for overlapping hours? Right now I have to inspect all the records for a particular date and id number to see if one range of hours is within another range or overlaps the range. How can I get Access to do this?
    Thank you.
    [/QUOTE]


    First you have to get the number of entries on a particular date for that particular ID (which I assume is a person's ID).
    This can be done be using a recordset.

    Then you can declare 3 variables :
    realStarttime
    realEndtime
    totalWorkingTime

    I suppose your entries are stored in the date/time-format.
    Take the first starttime and check if it's smaller than the second starttime. If yes realStarttime = first starttime else realStarttime = second starttime.

    Code:
    if starttime1 <= starttime2 then
         realStarttime = starttime1
    else
         realStarttime = starttime2
    end if
    Do the same with endtimes and for all the entries on a particular date for a particular ID.
    Every time you checked 2 times against each other you can calculate the real working time and assign it to totalWorkingTime.

    Hope this is a satisfying answer,
    If not you can send or post yout table and I will work it out for you.

  5. #5
    Join Date
    Oct 2003
    Posts
    3
    Thank you all for all your help. I think I have the problem solved due to your ideas.
    I run a series of tests in visual basic; for example one is below:

    If Me.EndTime < Me.StartTime Then
    Me.EndTime.BackColor = 255
    Me.StartTime.BackColor = 255
    Beep
    Msgbox("Check your entry. The end time is earlier than the start time.")
    Else
    Me.StartTime.BackColor = RGB(255, 255, 255)
    Me.EndTime.BackColor = RGB(255, 255, 255)
    End If

    I have one of these to cover every possible arrangement of start times and end times, standby times and work times. Then I created a sub and make all these tests run on the click event of a button.
    I'm still looking for a more elegant way to do this but this works for now
    Thanks again.

Posting Permissions

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