How do I update staff charge out rate without changing historical data?

[Jennytranxxx]

I am creating a timesheet system for my company and its got a pretty simple layout at the minute.
I am multiplying the staff charge out rate to the hours they have worked on a job.
Every now and then staff gets a payrise which increases their charge out rate.
I want to keep the calculated values that staff has spent on the project already and want the charge out rate to change as from the date their rate increased.
How would I be able to do this?

[NeoPa]

You need to include it in the design Jenny. IE. You can maintain a Default charge-out rate centrally, but each charge record needs to store the actual rate used.

[Jennytranxxx]

Thanks for your reply!
I don't really undersrand..
So I would need a table to store staff charge out rates, and add new charge out rates to it as they change?

[NeoPa]

I can't explain much more directly as I have so little information as to what you're working with/within.

You would need to store a charge-out rate somewhere at a point which is not date or time related. This would be the (default) value to pick up when creating an item which is date or time related. If each member of staff has a record then such a record would be appropriate for holding the default value. When an item is created that uses such a charge, EG. a job is done by that member of staff at a particular time on a particular date, then an item would be created to reflect that which would have to include a charge value which would be picked up from the record for the member of staff, but would not be linked to it in any way thereafter. Thus, if the MoS's rate subsequently went up, the record for that charge would still reflect the historical rate.

[zmbd]

Jennytranxx

Having helped my Grandpa on the construction site I think I know what you're after, which should be the total labor charges against the project cost - is that correct?

-z

[Jennytranxxx]

Absolutely! thats exactly what I am after.
I have a:
Staff table - Staff ID, Name, COR (Charge out rate)
Project table - Project ID, Name, Description
Time on Project table - Time ID, Project ID, Staff ID, COR, Time on project
A query set up to multiply the time with the COR.


I've resorted to moving all the data from 'Time on Job' to a new table ('Time on Job Append') using an append query, then use an update query to update the charge out rate of the staff, clear the data from the 'Time on Job' table and start entering again. So everytime a new Charge out rate has changed, that is what I would do. There is probably a MUCH better method for me to do this isn't there?

[zmbd]

Jennytranxxx:

A few things about the field names:
It will make your life easier not to use any non-alphanumeric charactor other than the underscore in your field and table names. so your "Staff ID" becomes "Staff_ID" or "StaffID"

"Name" is a reserved word, there are a lot of common words that SQL and VBA use either as constants, functions, etc... "Date" is another one. I suggest changing "Name" to "StaffName" or anything else ("Date" could become "StartDate" etc...) Google "vba Access reserved words" if you want to find a list.

[tbl_TimeOnProje ct] - how are you using this table?
-- what value are you storing in [tbl_TimeOnProje ct]![TimeID]
-- is the [tbl_TimeOnProje ct]![COR] field linked to the [tbl_staff]![COR]
-- are there more than one record per [tbl_staff]![StaffID] per [tbl_project]![ProjectID]

[NeoPa]

Jenny, this is so much easier to work with now all the relevant information has been posted (See your post #6).

From that I can't help wondering why you're asking the question. You already seem to have a data structure set up to handle your problem. It's hard to imagine that's in the design yet you, the designer, don't know how to use it. Not that it's important, just confusing for me.

Your [Time on Project] table has a [COR] field which is set at the time the data is entered for each individual record. It may come originally from the [COR] field from a [Staff] record, but when [COR] changes in the [Staff] record the [COR] field in the [Time on Project] record typically wouldn't change. This gives you an accurate historical position of all the [COR] values. Does this make sense?

[zmbd]

Jenny,
Is NeoPa correct in that the [Time On Project]![COR] as he has stated or is it a linked field?

-z

[Jennytranxxx]

I'm really sorry for the confusion, I haven't used Access in 6 years and all has been forgotten so I may probably have got alot of this wrong.

zmbd - Sorry I was just stating briefly the field names here, I understand the 'name' and 'date' rule, I have the correct field names on my db.
[tbl_TimeOnProje ct]![TimeID] is an autonumber.
[tbl_TimeOnProje ct]has the following field-
Time ID
Project Number
Staff ID
Week end
Stage
Time
Unpaid time
There is no COR field in this table. I have confused everyone. I'm sorry.
1 staff can work on may jobs.
I used the following query to enter data:
Job number
weekend
staff ID
forename
Surname
COR
Stage
Time
Total cost :[COR]*[Time]
forename, surname COR will be populated when Staff ID is chosen.

does this make sense?

[NeoPa]

Jenny:
does this make sense?

Sort of. It makes it clearer why you didn't see the solution if the design is as you describe it in post #10 certainly.

I would change the design to match the one you mistakenly posted earlier (for the reasons already made clear in post #8). This will give you what you have requested - the ability to keep both a current [COR] for each member of [Staff], as well as the ability to report correctly on historical transactions.

[zmbd]

NeoPa,
You beat me to it! :)

z

[Jennytranxxx]

Will do.
Thanks so much for your help! thank you

[NeoPa]

@Jenny - It's a pleasure :-)
@zmbd - Jenny's post #6 accidentally answered the question for me really. I just had to say "Yes. Do that."