Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compare hrs worked to scheduled hrs. (2000 SR1)

    I am trying to set up a database that will compare the total hours worked per day to the scheduled hours for the day for each Client. Example would be, 24 hrs scheduled for Sat. and Sun., and 16 hrs for Mon. through Fri. Then, if the sum of the hrs work for the day did not equal the scheduled hrs, a message box would pop-up saying the hrs worked did not match scheduled hrs. So far, I have tables for Clients, Employees, and Time Card Hours.

    The first question I have is if I should use another table for scheduled hrs for a day, or include this data in the Client table.

    I have created a main form using the Client table data. On it, I have a TimeCard subform for the hrs worked by each employee each day using data based on the Time Card Hours table. On the TimeCard subform, I have another subform - Day Hrs - that uses a query to sum the hours by day.

    Here is where I'm having a major problem in that I can not figure out how to compare the sum of Day hrs to the scheduled hrs for the day.

    I hope that I've explained my dilemma properly and Thank you for assistance.
    Jim

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    First question:

    If scheduled hours will never change for a client, you might add 7 fields to the Clients table (one for each day of the week).

    But if they can change over time, you'd need a table with
    ClientID
    Day of week
    Scheduled hours
    Indication of period (start date and/or end date)

    Second question:

    Depends on how you implement the first question. You can get the number of scheduled hours in a query, or perhaps using a DLookup.

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Hans,
    In my playing around with this - possible wrong choice of words - I've made a Client table with the scheduled hrs and a separate table with the scheduled hrs so that I would be able to try different ways. While we know that the scheduled daily hrs will change at some time and that there might be a need for more or less hours for a particular day, all I want to make sure that the data entry person know if they need to check the total hours entered for a day.

    For discussion, could we work with the scheduled hrs as part of the Client table. The first thing would be, where do I fire the compare check? I can think of several possibilities using one of the Event properties for TimeCard subform Date or HoursWorked fields, all which could cause the "Check Hours" message box popping up all the time. Could use the OnClose event for the main form which I think would be the best.

    As stated previously, my Day Hrs subform works from a query to get the total hours for a day. I know that I can use Weekday(DateWorked) to get 1 thru 7, but I can not figure out the code needed to compare the TotalHrs value to the correct field (Sun - Sat) in the Client table. I know that I'm not explaining this clearly, but I hope you can follow me.

    Thank you for your assistance
    Jim

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    If you use an event of the main form, only the day currently selected in the subform will be checked, unless you write code that loops through the days (records) in the subform.

    To compare, you might use something like (this is air code):

    Dim intHrs2Compare As Integer
    Select Case Weekday(subformname!DateWorked)
    Case 1
    intHrs2Compare = Sun
    Case 2
    intHrs2Compare = Mon
    ' etc.
    Case 7
    intHrs2Compare = Sat
    End Select
    If subformname!TotalHrs <> intHrs2Compare Then
    ' Do something
    End If

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Hans,
    Thank You very much. As for "this is air code", this fits, "air code" for an air head!! I see you used Case for the example. I thought of Case and also IF...ElseIf. What would be the reason for using one over the other, if any?

    Now the only other thing I need to get working is the Requery for the the Day Hrs subform query. I think that I'm not addressing the query properly.

    Thank You again. I'm sure I'll be back for other things
    Jim

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Hello Jim,

    The advantage of a case statement is that you don't have to repeat so much:

    If Weekday(subformname!DateWorked) = 1 Then
    intHrs2Compare = Sun
    ElseIf Weekday(subformname!DateWorked) = 2 Then
    intHrs2Compare = Mon
    ElseIf Weekday(subformname!DateWorked) = 3 Then
    intHrs2Compare = Tue
    ElseIf Weekday(subformname!DateWorked) = 4 Then
    intHrs2Compare = Wed
    ElseIf Weekday(subformname!DateWorked) = 5 Then
    intHrs2Compare = Thu
    ElseIf Weekday(subformname!DateWorked) = 6 Then
    intHrs2Compare = Fri
    ElseIf Weekday(subformname!DateWorked) = 7 Then
    intHrs2Compare = Sat
    End If

    does the same as

    Select Case Weekday(subformname!DateWorked)
    Case 1
    intHrs2Compare = Sun
    Case 2
    intHrs2Compare = Mon
    Case 3
    intHrs2Compare = Tue
    Case 4
    intHrs2Compare = Wed
    Case 5
    intHrs2Compare = Thu
    Case 6
    intHrs2Compare = Fri
    Case 7
    intHrs2Compare = Sat
    End Select

    The Select Case ... End Select looks less cluttered to me.

    Regards, Hans

  7. #7
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Hans,

    Thank you for the education lesson. I see what you mean by being "less cluttered" and understand that they will do the same.

    Jim

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Or if you knew the values (of the weekdays in this case) were contiguous you could use,

    Dim intHrs2Compare As Integer
    intHrs2Compare = Choose(Weekday(subformname!DateWorked),Sun,Mon,Tue ,Wed,Thu,Fri,Sat)
    If subformname!TotalHrs <> intHrs2Compare Then
    ' Do something
    End If

    Cheers,
    Pat

  9. #9
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compare hrs worked to scheduled hrs. (2000 SR1)

    Thanks Pat,

    Will have a go at this along with what Hans has sent.
    Jim

Posting Permissions

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