Update salary but still make calcuations with old salary 1

[ldoublee75]

I have a database that performs calculations in several different queries. I need to update salaries throughout the year but still have my queries calculate from the old $ and the new $. Is there an easy quick answer to this or is this something I need to put a lot of work into?
Thanks for you input!
ldoublee

 

[SkipVought]

Hi,

So do you have a field for Old$?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ldoublee75]

No, as of right now I just have a field for salary.

 

[SkipVought]

So how do you expect to have the value of the Old Salary if it's not in your database?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ldoublee75]

Right on, I get ya! So then I reprogram my queries?
I appreciate the insight by the way!

 

[SkipVought]

Unless you are ONLY using Old $ in the same process where you are updating the salary, I was inferring that you need to add a column to your table for Old Salary.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[lespaul]

or create a new record each time the salary changes and keep a history.

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

 

[SkipVought]

Les, there you go!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[ldoublee75]

I got what you was inferring. I was assuming that now i have two columns for salary i need to reprogram my query to look for a new salary and date it takes affect when it makes the calculations. I'm a little confused on the implications in the future of having all of these old salaries also. Am i making sense?

 

[lespaul]

I was assuming that now i have two columns for salary i need to reprogram my query to look for a new salary and date it takes affect when it makes the calculations.

You mean you're planning something like:

CurrentSalary EffectiveDate OldSalary

what happens when a person is changed again? Add another field for previousOldSalary? (read the fundamentals document linked below for more on why this is a bad idea).

Better to have a new record for each one and when you just need the current info look for the latest date; if you need the history get all records for that person.



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

 

[ldoublee75]

How do you suggest I do this? I'm confused by what you mean to make another record.
By the way I have read the fundamentals document and understand it, I think I'm not conveying my question correctly, sorry.

 

[lespaul]

Each time a person gets a new salary enter a new record:

EMPID SALARY EFFECTIVEDATE
1 20000 1/1/2005
2 25000 2/10/2005
1 25000 6/15/2005

SELECT EMPID, SALARY, MAX(EFFECTIVEDATE) FROM TableName GROUP BY EMPID, SALARY

will return
EMPID SALARY EFFECTIVEDATE
1 25000 6/15/2005
2 25000 2/10/2005

 

[ldoublee75]

I got ya! Sorry for being so dense, I appreciate you help!