running balance per employee

[brorick]

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.

[OBP]

brorick, I am not sure about the grouping part of it, but I have a "Difference" between Records" Query/function which Jimmy the Hand created for me, it is the one called Difference.
I have added the Running sum version to it and it is called "Plus".

[JimmyTheHand]

Hi
I believe your example data is inconsistent. I marked (with red bold font) the parts which, I suspect, are wrong. Please correct or confim them. If they are all right, then I unfortunately don't follow the logic.

Jimmy


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-------20
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-------44

[brorick]

OBP, and JimmyTheHand thank you for your help. I will take a look at the attachment. Jimmy thank you for pointing out my typo. You are correct. It was wrong. I have since corrected the sample data.

[JimmyTheHand]

If you can't get it straight with the help of that attachment, please post the table and field names this query is made out of.