brorick
06-02-2008, 04:31 PM
I have a query based on my employee status table(Tbl_EmpStats). I need to create the column titled Earned Points. I am having a difficult time creating a type of running balance for each Job in my table based on the following logic.
- If the percent is the same as the previous week the employee does not earn any points.
- Eanred Points calculation is based on the previous weeks accumalative total of earned points subtracted from the current total grouped by Job.
Date-----Job-------Employee---JobPoints----Percent---Total----EarnedPoints
1/1/08---11001-----John-------100-----------20%------20-------20
1/7/08---11001-----John-------100-----------20%------20-------0
1/15/08--11001-----John-------100-----------50%------50-------30
1/21/08--11001-----John-------100-----------75%------75-------25
1/28/08--11001-----John-------100-----------100%-----100------25
1/1/08---12020-----Bill-------80------------0%-------0--------0
1/5/08---12020-----Bill-------80------------25%------20-------20
1/12/08--12020-----Bill-------80------------70%------56-------36
1/18/08--12020-----Bill-------80------------70%------56-------0
1/18/08--12020-----Bill-------80------------100%-----80-------24
I have racked my brain over the past several days trying to come up with a query or function. No luck. :mkay Any help would be greatly appreciated. Thank you in advance.
- If the percent is the same as the previous week the employee does not earn any points.
- Eanred Points calculation is based on the previous weeks accumalative total of earned points subtracted from the current total grouped by Job.
Date-----Job-------Employee---JobPoints----Percent---Total----EarnedPoints
1/1/08---11001-----John-------100-----------20%------20-------20
1/7/08---11001-----John-------100-----------20%------20-------0
1/15/08--11001-----John-------100-----------50%------50-------30
1/21/08--11001-----John-------100-----------75%------75-------25
1/28/08--11001-----John-------100-----------100%-----100------25
1/1/08---12020-----Bill-------80------------0%-------0--------0
1/5/08---12020-----Bill-------80------------25%------20-------20
1/12/08--12020-----Bill-------80------------70%------56-------36
1/18/08--12020-----Bill-------80------------70%------56-------0
1/18/08--12020-----Bill-------80------------100%-----80-------24
I have racked my brain over the past several days trying to come up with a query or function. No luck. :mkay Any help would be greatly appreciated. Thank you in advance.