Thanks

2. ## 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)

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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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:

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?

11. ## 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. ## 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. ## Re: Labor Rate Updates (2003)

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

14. ## 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
•