changes made to update from a certain date 1

[gscma1]

I have a database which calculates employee wages. it has a table for employee details including wage rate, name address etc... tblemployees. I have another table, tblhours, where employees hours are entered each week, therefore foreign key from tblemployees is employee id.

It is possible that employees have a pay rise, this can happen at any point in the year.

how would i go about putting the increase into tblemployees so that my reports still print off with the old wage rate up until the rate change date.

the master wage sheet is printed everyweek, however i have several reports which print off the period totals (4consecutive weeks) currently I have one field for wage rate and therefore if there is an increase half way through a period all week totals take that increase.

I was thinking i need to fields for wage rate, e.g. current/previous wage rate and updated wage rate. I also thought i should have a field for week of change. however i am not sure how to incorporate this into my current reports etc...

Hope this makes sense and someone can help me.

 

[lespaul]

what you need is 'tblEmployeeWages' :

ID
EmployeeID
EffectiveDate
WageAmount

then each time someone gets a raise, you can add a new entry and have correct information based on the date.

The reason you are having this issue is that the table structure isn't normalized. You have Employees and you have Wages, you have stored information about wages in a table about employees. Read the fundamentals document linked below for more information about normalization and how tables should be designed.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

i see where your coming from, however how will that solve the problem of wages prior to the effective date? would i need to have a previous wage rate field? there will still be data prior to the new wage rate that needs to be worked out at the previous wage rate.

thanks

 

[lespaul]

everytime a person gets an increase, there will be a new record. You will need to join into this new table based on the date.

Say you have
tblEmployees
ID (PK)
Name


tblEmployeeWages
ID (PK)
EmployeeID (FK)
Wage
EffectiveDate

each time an employee gets a new wage, a new record is inserted in tblEmployeeWages:

ID EmpID wage EffectiveDate
1 1 15.00 1/1/2006
2 2 12.50 1/1/2006
3 1 17.00 6/1/2006

So employee 1 got a $2/hour raise on 6/1/2006

Now, your reports can be set up to run based on the wage on a specific time frame.

Does that help?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

Could anyone please tell me how to set a report to use the standard wage rate which is within tblEmployees but to use the new wage rate if necessary from tblEmpWageIncrease.

I am confused at how to make the report look at both tables and use the wage rate as of an effective date.

Please help

 

[lespaul]

you should really move the Wage field out of the Employee Table.....you should have:
[tt]
TblEmployee
ID (PK)
Name
Address
StartDate
TerminationDate
(anything else about the EMPLOYEE and NOTHING about the Employee's Wages which is a differnt entity therefore requires its own table)

tblEmployeeWages
EmployeeID (FK)
EffectiveDate
WageAmount
[/tt]
There should be no information about an Employee's Wages in the Employee Table. The first entry in tblEmployeeWages for each employee the EffectiveDate of the wage will be the same as the start date for the employee.

Let's say EmployeeID 100 started on 1/1/2006 you would have:
[tt]
tblEmployee
ID Name Address startDate TerminationDate
100 Joe Blow 123 Main St 1/1/2006

tblEmployeeWages
EmpID EffectiveDate WagePerHour
100 1/1/2006 15.00
[/tt]
now after 6 months, Joe gets a raise, a new entry:
[tt]
tblEmployeeWages
EmpID EffectiveDate WagePerHour
100 7/1/2006 16.50
[/tt]

With a structure as outlined above, what you want to do is not complicated. If however you can not or do not want to change your structure and you really want to write a query that looks at both tables, you should provide us with field names and sample data along with your expected results.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

ok, that makes sense, i don't mind changing the structure as it makes more sense how you have explained. once i do this i am unsure how to get the report to pick up the correct wage rate according to the date.

I'll get onto changing my structure and see how i go

 

[lespaul]

SELECT * FROM TblEmployees E INNER JOIN TblEmployeeWages W ON E.EmployeeID = W.EmployeeID

this query will return:
[tt]
ID Name Address startDate TerminationDate EmpID EffectiveDate WagePerHour
100 Joe Blow 123 Main St 1/1/2006 100 1/1/2006 15.00
100 Joe Blow 123 Main St 1/1/2006 100 7/1/2006 16.50[/tt]

now depending on the report structure you can join or filter where the criteria date is > or < than the effective date

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

I have now set-up, tblEmployeeWages complete with the correct data.

Now my existing report is a Master Wage Sheet, it draws information from a parameter query - asking Which Week Number information is required from. this query takes information from the following tables:

tblEmployees (Forename;Surname;WageRate)
tblHours (WeekNo;EmpId;Hours;Cash;Pricework)

Pricework is when the employee is given a price rather than his/her normal daily rate for a particular job.

My report calculates the Gross Wage using [Hours]*[WageRate]+[Pricework]

I have a table set up which is week number against date, therefore i can add to this query the date from tblWeeksDates.

How would you suggest I incorporate the new tblEmployeeWages into this using the wage effective date?

It is important that i retrieve previous Master Wage Sheets whenever necessary, obviously with the correct wage rate showing.

hope this is enough information, i've tried to be as detailed as possible about my database, i am a bit of a novice.

Many Thanks for any help in advance

 

[lespaul]

ok, first you need to remove WageRate from tblEmployees.....I see EmpID in tblHours, but no ID in tblEMployee....do you have EmpID in tblEmployees? That should be your PK and should be in most of the tables that store any information about the employee.

I'm going to assume that you have:

tblEmployees
ID
Forename
SurName
Address....

tblEmployeeWages
EmployeeID {
EffectiveDate{composite PK}
WageRate

tblHours
ID (PK)
WeekNo (FK)
EmpID (FK)
Hours
Cash (??)
PriceWork

tblWeeksDate
WeekID (PK)
StartDate

And you want to report
EmpID Forename SurName GrossWages
100 Joe Blow #######

I'm having difficulty working it out in a single query, so here's a multiple query attempt:

qryEmployeeWages
SELECT EmpID, Max(WageRate) FROM tblEmployeeWages W WHERE EffectiveDate <= (SELECT StartDate FROM tblWeeksDate WHERE WeekID = [Please enter the week to report])

qryGrossWageReport
SELECT ID, ForeName, SurName, (Hours*WageRage) + PriceWork As GrossWages FROM Employee E
INNER JOIN qryEmployeeWages W on W.EmployeeID = E.ID
INNER JOIN tblHours H ON H.EmpID = E.EmpID

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[gscma1]

Thank you so much for all your help

how do you do a multiple query, i can't understand where i write this, is it within the report itself?

qryEmployeeWages
SELECT EmpID, Max(WageRate) FROM tblEmployeeWages W WHERE EffectiveDate <= (SELECT StartDate FROM tblWeeksDate WHERE WeekID = [Please enter the week to report])

qryGrossWageReport
SELECT ID, ForeName, SurName, (Hours*WageRage) + PriceWork As GrossWages FROM Employee E
INNER JOIN qryEmployeeWages W on W.EmployeeID = E.ID
INNER JOIN tblHours H ON H.EmpID = E.EmpID

sorry about this, i am a novice and this confuses me, i have basically taught myself from scratch however when there is something new to learn i need a bit of guidance to understand.

 

[abaldwin]

I know this is late in the game and you all have done some work with your structure, but I thought I would throw out something to you.

I would leave the current wage in the db. Create another table to hold old wages. (for history only with effective dates)

In the hours table grab the current wage from teh emp table and write to the hours table as well. The reports would change to calculate hours * wage from one table, The emp table would show the current wage, the history table would track any changes up or down, and all reports work from now on and going back as well.

Just my $.02, most HR type systems have requirements to maintain a history capability of tracking things like wage changes.




Andy Baldwin

"Testing is the most overlooked programming language on the books!"

Ask a great question, get a great answer. Ask a vague question, get a vague answer.
Find out how to get great answers FAQ219-2884.

 

[gscma1]

andy, your method does seem a lot simpler, my confusion is, how do i get my report to look at both wage rates in the two tables and decide which one to use?