Suggestions for updating data in a field upon which calculations depen 1

[beverlee]

I have come upon this problem a few different times in various databases. I'll use hourly rate as an example. I have an employees table which includes their hourly rate of pay, and I have reports written to calculate job cost, that depend on the hourly rate. Now the employee gets a raise and the hourly rate is changed in the employee table. If I need to run a report to calculate job cost from a month ago, it will calculate at the new rate when it should actually be calculating at the old rate.

Will I need to save the rate to a field in the job detail record and pull it from there instead? Any suggestions for dilemmas such as this?

Thanks!

 

[randysmid]

Hi,
I actually had a problem similar to this. In my case, employees belong to Departments (or Cost Centers), and they report their activities on time sheets (but not used for payroll purposes). So, they could turn in their time sheets for any time period during the entire fiscal year.

My solution was to create a Personnel_assignments table that had SSN, CostCenterNumber, start date, and end date. That way, I could check any given time sheet for the exact date, and then know what CostCenter to charge the time to.

In your case, I would create a new table containing hourly pay rate history, again with SSN, pay rate, start and end dates. Are you starting to see the picture? Let me know if you want further details on how to implement this, ok?

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[beverlee]

Okay, so I create a new table with the fields EmployeeID, HourlyRate, Start Date, and End Date. I will link it to my Employee table by EmployeeID and put a subform on my Employee form to hold this Pay Rate History.

Now in my reports, I need to edit my record source. Do I place a query within a query so that the correct hourly rate is pulled?

Thanks for the help so far!

 

[randysmid]

Hi,
Presumably your report is based on a query, right? I presume that your job costing is based on more than one employee, correct? Well, I would create a query that first creates a list of employees who have PayRateHistory records for the job costing date. Your query will need to use criteria that limits the PayRateHistory record to match that of the JobCosting date. Assuming that this date appears on a form where the user can enter the report criteria, the criteria for your PayRateHistory "StartDate" field would be:
>=[Forms]![frmJobCostingRept]![JobCostDate]

I am certain that you can create the criteria for the "EndDate". Your first query will now have a complete list of employees and their pay rates for the job date in question. Is this what you need?

You may need another query to link in all those individuals who are represented in the job costing proposal. You can link the first query to this 2nd one based on EmployeeID, and this will give you the hourly rate of those people based on the JobCosting date.

Now, your final query will be linked to the second query that we just created. Make sense so far? With this, you will have all the necessary fields available to the report, so that the cost can be calculated correctly.

Unfortunately, you will now need to add a record (or more) for each employee into the Pay Rate History table. You will need to make sure that you don't have any gaps in the dates, e.g., 1st start date of 01/01/2003, 1st end date of 12/31/2003, 2nd start date of 01/15/2004, etc.


HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[beverlee]

Thanks for the help - greatly appreciated!