Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Labor Rate Updates (2003)

    Perhaps one of you can point me in the right direction or at least give me short lesson in database design for efficiency. I have created a fairly simple database to track labor hours and associated costs. My original design didn't take into account the adjustments required for labor rates each year. I created a table with the employee name, employee category, and associated billing rate. I had also created a table for storing labor hours expended on each task that could have data entered daily, weekly or monthly. The end result of my reports are to calculate, by task, the total cost for the month by category. This works great, however I am now faced with updating the rates for the new year. As an added twist, we just picked up a new set of employees that have a basic billing rate and they get an overhead charged to their labor each month. Example: Employee 1 labor rate $125/hr; Employee 2 labor rate $160/hr; Employee 3 labor rate $85/hr. Employee 3 gets the overhead charge tacked onto the labor rate each month in the form of an overhead indicator that might be something to the effect of 1.05. This is simply a multiplying factor on the labor rate of Employee 3 and about of a third of our employees each month. i.e. $85*1.05=$89.25 for the month. It will be the same factor for all of the 'overhead' employees for the month, but will change every month. I want to be able to store this information into the database for history purposes and for the ability to run quarterly analysis reports and verification of total cost at the end of each year. I can imagine a table of our ~90 employees with a column for each month's rate. I am not sure this is the most effective means to do this and would like some simple advice for the simple minded like me...

    Thanks

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

    Re: Labor Rate Updates (2003)

    Instead of using separate fields (columns) for each month's rate, use separate records. Create a table with fields:

    EmployeeID (numeric, I assume)
    StartDate (date/time)
    Rate (currency)
    Overhead (yes/no)

    When the rate for an employee changes, add a new record.

    In another table, you can store the overhead factors:

    StartDate (date/time)
    Multiplier (number, single or double)

    You can then use queries to determine the actual rate for a specific employee in a specific month.

  3. #3
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Hans,
    Thanks as usual for the speedy reply. One question to follow on: Will this work effectively to calculate across the multiple dates when I run a quarterly report? Some employees will have three records with different rates applied to many records in the labor recording table over the three months. The database would then have to calculate Month1, Month2, Month3 then add for the total. This will be keying off the StartDate field for each record that applies to the chosen time frame, correct? Like I said, simple minds... I will work on this and let you know if I have more questions as I get it closer.
    Thanks

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

    Re: Labor Rate Updates (2003)

    You'll also need a table listing all months (as dates, e.g. December 2005 is represented by 12/01/2005). You can use this to get a record for each month in a quarter in a query.

  5. #5
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Hans,
    Almost there. How do I tell a query to look back to the last posted rate in the employee rate table for the future months? If the rate starting in Oct 05 won't change for a year, how do I pull the October rate when I do the December rate calculation? Did I tell you this feels like a Monday? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

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

    Re: Labor Rate Updates (2003)

    You can do this in two steps:

    1) Determine the latest starting date:
    Create a query that returns only the employee id and the starting dates.
    Turn the query into a Totals query (View | Totals).
    Set the Total option for the date field to Max.
    Set the criteria for the date field the way you want, for example

    <=#12/01/2005#

    You can also use a parameter:

    <=[Enter report date]

    or refer to a text box on a form:

    <=[Forms]![frmSomething]![txtDate]

    2) You can combine the query from step 1 with other tables/queries in a new query.

  7. #7
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Hans,

    I have created the query as you recommended and filled a table with updated rates for last year and this year (10/1/2004 and 10/1/2005). When I run the query with the parameter of 12/1/05, I get all the lastest dates. If I run the query with the parameter of 8/1/2005, I only get the rates that were not updated 10/1/2005. Only about half of our labor rates were adjusted 10/1/2005. So I have 37 rates for 10/1/2004 and updated all but 12 for 10/1/2005. So I get the 12 records from 2004 for only those that were not updated. Am I screwing this up or what am I doing wrong? I want to be able to pull a list of rates for each month as we will be having about 30 employees added to this that get updated each and every month.

    Query:
    SELECT tblRate.EmployeeID, Max(tblRate.StartDate) AS MaxOfStartDate
    FROM tblRate
    GROUP BY tblRate.EmployeeID
    HAVING (((Max(tblRate.StartDate))<=[Enter Report Date]));

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

    Re: Labor Rate Updates (2003)

    Try

    SELECT tblRate.EmployeeID, Max(tblRate.StartDate) AS MaxOfStartDate
    FROM tblRate
    WHERE tblRate.StartDate<=[Enter Report Date]
    GROUP BY tblRate.EmployeeID

  9. #9
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Sorry for the delay in testing and responding, busy this time of year... Anyway, thanks Hans, this works as expected. I have been thrown a curve and will be receiving data in a new format beginning in January, but shouldn't effect the way this calculation needs to work. So many thanks as always. <img src=/S/king.gif border=0 alt=king width=21 heig ht=22>

  10. #10
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Hans,

    I have run into another issue with the final query to pull records after a rate change. Here is my query:

    SELECT tblTasks.WBS, tblTime_Billed.BillableHours, tblEmployees.LastName, tblTime_Billed.BillingDate, qryCurrentRate.Rate, [BillableHours]*[qryCurrentRate.Rate] AS Cost, tblTasks.Description, tblEmployees.CategoryName
    FROM (((qryCurrentRateDate INNER JOIN tblEmployees ON qryCurrentRateDate.EmployeeID = tblEmployees.EmployeeID) INNER JOIN qryCurrentRate ON tblEmployees.EmployeeID = qryCurrentRate.EmployeeID) INNER JOIN (tblTasks INNER JOIN tblTime_Billed ON tblTasks.Charge_Number = tblTime_Billed.Charge_Number) ON tblEmployees.EmployeeID = tblTime_Billed.EmployeeID) INNER JOIN tblRate ON tblEmployees.EmployeeID = tblRate.EmployeeID
    WHERE (((tblTime_Billed.BillingDate) Between [start] And [End]));

    The query results in 2 records for each entry in the tblTime_Billed table for each person in tblEmployees that had a rate change in the tblRate table (This is assuming there are 2 entries in the rate table for this employee) If I add another rate to the rate table for this employee, I get 3 records from the tblTime_Billed table for this person. So the result is giving me 2 or more entries for each date that a record is entered in the tblTime_Billed table. It is picking up the count of records from the tblRate table and applying it to this query for some reason.

    Is that clear enough?

    Thanks in advance as usual...

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

    Re: Labor Rate Updates (2003)

    This is too complicated to comment on without seeing the database. If you wish, you can post a stripped down copy of your database. See <post#=401925>post 401925</post#> for instructions.

  12. #12
    Lounger
    Join Date
    Feb 2005
    Location
    Albuquerque, New Mexico, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Labor Rate Updates (2003)

    Thanks, try this. The problem is evident when you run a November or December 2005 MonthlyWBSCharges query. Some of the records have more than one result for the same WBS.

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

    Re: Labor Rate Updates (2003)

    I'll look at it later today, if nobody else reacts.

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

    Re: Labor Rate Updates (2003)

    You should remove tblRate and qryCurrentRateDate from the design of qryMonthlyWBSCharges. They serve no purpose there, since you already have qryCurrentRate in the query.

Posting Permissions

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