change pay rate in job cost database without changing historical records

[St.Alphonzo]

Hello... Access newb here and first actual cry for help in a forum ever so thanks in advance for taking it easy on me.



Anyhoo, I am giving slow painful birth to a database that tracks job related costs for a general contracting company. Sub contractors, materials, etc. are entered into tbl_JobCosts and Employee timecards are entered into tbl_EmployeeHours. traditionally all of this was handled in an Excel spread sheet that was wrought with typographical errors as well as mathematical errors, hence the migration to a more normalized and automated number cruncher. however, one hurtle I can't seem to overcome (at least without creating another field to enter) is the occasional change in an employee's wage, or billing rate. Currently, I have a query that draws from tbl_EmployeeHours and calculates gross wages and billable cost according to tbl_Employees (where I have names and pay rates) and tbl_CostCode (labor type and billing rate) using a nested IIf formula.

But what happens if someone gets a raise? or there is a change in billing rate, going forward? I know the answer lies in the design of my query and I have accounted for the billing rate anamoly (as for one of our projects, our site super is being billed with no mark up, so I created a new cost code - a field we already have to enter to distinguish labor from subs, materials, rentals, etc)... but as I am entering historical data from the spreadsheet into the new Access DB, one of our employees received a raise halfway through the project... since my "Labor Cost" is determined by multiplying hours by a rate specified in tbl_Employees, is there a function by which I can automate the usage of a new rate, when entered, while keeping old records what they are? right now the only idea I have is to employ an "Employee Number" or more aptly put, "pay scale number" which would change only when there is a change in pay rate. But I feel like this is Excel logic.

I know this all may sound horribly unclear, but I'm hoping my hang-up lies within my limited knowledge of SQL and not in my general database design....

[pbaldy]

Well, most commonly this is done with a design tweak. The applicable rate would be stored along with hours in your activity table. It's similar to a product sales application, where you would store price along with quantity in an order details table.

[St.Alphonzo]

I think I got it using a date range function in my query. Thank you for the quick reply.... I'm still storing the pay rate in the "Employees" table, but the Labor Cost query will return different rate calculations based on the date of entry:

Sum(IIf([EmployeeHours]![_Date]<#7/1/2014#,[EmployeeHours]![Hours]*[Employees]![Hourly Rate 1],IIf([EmployeeHours]![_Date]>#7/1/2014#,[EmployeeHours]![Hours]*[Employees]![Hourly Rate 2])))

I knew it was something simple, but sometimes just asking the question leads to an answer.

as for if/when there's another change, I suppose I'll amend this code accordingly. as far as general design, I am curious as to how I could more automatically build a change like this into the functionality of a database without a phrase and field for each date range/pay rate.

Thanks for the quick the reply!

[pbaldy]

That's simple? I would definitely not have a field for each time period like that; what's that formula going to look like in a couple of years? If you don't want to store the rate like most people would, you can have a rates table with fields for the start and end dates plus the rate. Then you can use a DLookup() or non-equi join in a query to get the rate. Both of those are more complicated than storing the rate with the transaction and being done with it.

[St.Alphonzo]

I see what you're saying... That's essentially what I have, a rates table [Employees] where the rates are stored [Hourly Rate 1], [Hourly Rate 2] for each Employee, which is referenced in the [Labor Cost] query according to date via the formula above.... as they change infrequently, I'm trying to limit the number of fields that need input for each transaction... but just to be clear, you are saying to "store", in other words have an input field in the [Employee Hours] table that determines the rate?

[June7]

I agree - a field for each pay rate is not normalized structure and will cause lots of issues, such as the one identified by Paul. Repeatedly modifying db just to handle new data is poor design.

Conventional approach options:

1. save the rate into each record - by code or user input

2. create a new record for each rate and save the record ID - can be selected from a combobox - retrieve the associated rate in query by joining tables

3. DLookup() or non-equi join (actually a new term for me - not quite sure what this query structure is but might also be known as Cartesian relation)

[pbaldy]

You do NOT want numbered fields like [Hourly Rate 1], [Hourly Rate 2].

... non-equi join (actually a new term for me - not quite sure what this query structure is but might also be known as Cartesian relation)

No, not the same. It's where the join in a query isn't simply "=". For example in this situation, given a rates table with BeginDate and EndDate fields for the effective dates:

SELECT Blah, R.Rate
FROM TransactionTable AS T LEFT JOIN RatesTable AS R ON T.TransDate >= R.BeginDate AND t.TransDate <= R.EndDate

In words, it will return the rate effective on the transaction date. Personally I'd simply save the rate with the transaction.

[St.Alphonzo]

well I do have a rates table, called [Employees]. The two fields are [Employee] and [Hourly Rate]. In the query that sums data from the [Employee Hours] table, Labor Cost is calculated by referencing the [Hourly Rate] field and multiplying that by the total sum of hours for each Employee under each Job Component number.... since there is more than one rate for a given time frame (and potentially more than one Billing Rate for a given employee a given time frame, depending on the stipulations of the Job contract), I thought that keeping those semi static numbers in a separate table would be better than relying on the data entry guy to remember everyone's individual pay rates, etc.

I don't quite understand DLookup yet though... or what you mean by "store" the rate with the transaction.... I don't want the person entering data to have to specify a rate or value to determine the labor cost (other than the name of the employee)... which is what led me to using this date range filter to accommodate the changes...

but with DLookup, if I understand correctly, the pay rate would be a calculated field in the [EmployeeHours] table (where time cards are input), which would multiply the value from the [Hours] field, by the value determined by DLookup("[Hourly Rate]", "[Employees]", "[Employee]='John Doe'")?

this would still affect historical records if I change the [Hourly Rate] value, no?

[pbaldy]

You have a field for hours in your activity table (not sure of the name); I'm saying add a field for rate and populate it when you create a transaction. That way it's saved with the transaction instead of in the employee table.

[June7]

Code could do a lookup on the rates table to find the appropriate record based on criteria of employee ID and save the rate into record - option 1.

Or the value would not be changed, a new record with applicable date range would be created - option 2 or option 3. A DLookup would also need date range criteria. Would not be a calculated field in table - domain aggregate functions are not recognized by tables - would have to be in query or textbox.

So the 3 approach options are still what you need to choose from.

Names make poor unique identifiers - should use EmployeeID.

[St.Alphonzo]

ok.. thank you SO MUCH for the help guys, I'm gonna get more into this tomorrow...