Query challenge

pser

Registered User.
Local time
Today, 11:31
Joined
May 21, 2002
Messages
32
Hi, i have a query salary which contains a this: "SID PK, autonum", "personId FK, text", "year, 4 digit, text", "General raise, number", "personal raise", "percent increase, double", and "total sum, number"

Ok, here's the challenge. Let say they you start in 1999 with a salary, next year which is 2000 you get a general and personal raise, the total sum should then contain the sum from last year (1999) added with general and personal raise and be put into sum for year 2000 for that particular person. Increase in salary from last year should be calculated as well and be put in percent raise. Next year 2001, the same should happen only with the number from 2000 and so on. Is it possible to do is in a elegant way.

I've thought of a opportunity to solve with creating two recordset, one based on a query, then next on the table salary. Then i would go and calculate for each person... Problem would be that if there's allot of record this will take time.

Any suggestion are welcome..

Pser
 
I can do that query in ... three steps

(Name that query!)

Step 1. Make a query that selects all entries prior to the current year. Exclude any stuff from this year. Or at least, any stuff prior to a specific date.

Step 2. Make a DMax query based on the query from step 1. In the DMax, include a selection criterion of the value for the given personID.

This works even if the poor schlemiel being selected was passed over and didn't get a raise last year. The only time it fails is for someone who has never had a raise before, i.e. a new hire, or for someone who got DEMOTED and thereby lost salary. Both of which would have to be treated as special cases.
 
Thanks guys. So far i solved it with traverse two recordset, then so the calculation, insert it into table and then requery which will then get the correct result. This means double loop which i think will affect the speed with increased number of person register. I'll check out your suggestion Doc Man..

Pser
 

Users who are viewing this thread

Back
Top Bottom