Update hourly rates with exceptions that use a different list of rates.

[ParachuteTechnology]

tblTimeEntry records individual time slips for attorneys keeping track of their time. Each slip records the client, attorney, time, rate, and value (time * rate) of the slip.
tblPeople holds all the timekeepers and their current rate. Their rates change once a year.
tblCustomRate will hold the exceptions, and this is what I need help implementing.

How I imagine tblCustomRate will be set up is as follows (and this may be how it's wrong):

Columns for each timekeeper. Each record is a client entry.

When a new time entry gets entered, as it gets saved into tblTimeEntry, it should check to see if the client number is one that has a record on tblCustomRate. If it does, it should find the column for the timekeeper and use that rate.

I can't imagine how this works in my head. I could add columns to the tblPeople for each client that gets a custom rate and use IF statements to get it to add, but that's a lot of legwork and code for a few clients.

tblTimeEntry
TimeID (primary key)
ClientID
TKFN (Timekeeper First Name)
Rate
Value

tblPeople
PersonID (primary key)
TKFN
CurRate (Current rate)

If it matters, this is Access front end with SQL Express tables. It's few and far between that I set up new People and it would be few and far between that there would be any custom rates (currently, there would be 2 out of 2000). People's rates change, but the time slips they entered at their old rate should stay at their old rate. If they were only worth $100/hour in 2010, but now they're worth $200/hour, the 2010 slips stay at the $100/hour rate.

Thanks

 

[pbaldy]

If I'm reading right, I'd be saving PersonID in tblTimeEntry, not the name. Is this what you're looking for to get the rate?

http://www.baldyweb.com/Autofill.htm

 

[ParachuteTechnology]

If I'm reading right, I'd be saving PersonID in tblTimeEntry, not the name. Is this what you're looking for to get the rate?

In hindsight that would have been preferred, but TKFN works as the primary and foreign key here. I am already pulling the regular rate for 99% of the time entries based on a field in tblPeople. The 1% of cases that have special rates are the issue. The proposed solution would be a new, third table (I think). Here's how it could look:

tblExceptions
ExceptionID
ClientID
Col1: Matt
Col2: Mike
Col3: John
Col4: Carl

Records:

ID  ClientID      Matt  Mike  John  Carl
1   4008600001    150   200   275   100
2   4018100001    225   400   525   200

Then, when Mike enters time into ClientID 4008600001, it uses his rate of 200 instead of his standard rate of 100.

Does that explain it better?

Thanks!

 

[pbaldy]

Yes, but I'd normalize it with

Client Person Rate
4008600001 Matt 150
4008600001 Mike 200
etc

Which would be more dynamic when you add people. Then you can open a recordset or use DLookup() to find the rate for the client/person combination. If it comes up EOF/Null, you use the standard rate.

 

[Fran Lombard]

I would build the Exception table to have a separate record for each client/employee combination.

Additionally I would add an {Exception} flag to the client table to indicate which rate they use -standard/non-standard or both (some employees standard, some non) rate

tblExceptions
ExceptionID
ClientID
TKFN (Timekeeper First Name)
Rate

With this set up you would check the client client table to find which table to retrieve the rate then get the rate using the appropriate keys


Additionally I would consider moving your standard rates to a separate table and add an {EffectiveDate} column.
Hope this helps.