How to Subtract Last Month's Data from This Month's?

[MSSP94]

Hello everyone, what a wealth of knowledge I stumbled onto with this forum! I'm pretty new to Access so forgive me if this is has been covered in the past or it's simple.

I designed a database that keeps track of "points". Every time someone achieves 50 points they receive a bonus and are recognized for achieving that milestone for that month. Point totals are entered in once a month and that is how they are reflected on the reports. For example...

Sample Person 1
Month Points Received = 1
Points = 30

Sample Person 1
Month Points Received = 2
Points = 35

From this example, Sample Employee 1 hit his 50 point milestone in February and has a total of 65 points. He has 15 points left over... so if he earns 40 points in March he'd hit his milestone again, get his bonus, be recognized, and have 5 points credit towards April.

My problem is finding a way to make a query that identifies when the person hits 50 points, subtracts 50, and adds the reminder to next month's point total.

Back to Sample Person 1... So if

Sample Person 1
Month Points Received = 1
Points = 30
Point pool = 0

Sample Person 1
Month Points Received = 2
Points = 35
Point Pool = 0

Sample Person 1
Month Points Received = 3
Points = 40
Point Pool = 15
Points + Point Pool = 55 - he hits his goal

Sample Person 1
Month Points Received = 4
Points = 20
Point Pool = 5

Sample Person 1
Month Points Received = 5
Points = 30
Point Pool = 25 - He accumulated 25 because he didn't meet the goal last month + he had 5 points already in his pool. Since he earned 30 this month, he meets his goal and his Point Pool for June will be "5".


Hope this sort of explains what I'm trying to do. The Fields for this from my main table are -

[Name]
[PointsEarnedDate] - 2/14/2006, 3/06/2006, etc.
[Month] - Using the Format Function (January, February, March, etc for use on my report)
[Month1] - using DatePart ( 1,2,3,4,etc for sorting my Report )
[Points] - Points entered, it is split up using the [PointsEarnedDate] using a form.

Any help on this would be greatly appreciated!

Rob

 

[MSSP94]

Here's some more info...

The Main Table has the following fields...
______________________________________________________________
Entry ID | PersonID| PointEarnedDate | Process Date | Points
(Links to another table with all their info)

--1-----------MSSP94-------01/03/2006------01/06/2006----30
--2-----------MSSP94-------02/03/2006------02/06/2006----35
--3-----------MSSP94-------03/03/2006------03/06/2006----40
Etc., Etc.

The information is entered using a form. The [Process Date] is the "Date()" at the time the points were entered. The [PointEarnedDate] is the date they were actually earned what I use to determine what month they receive recognition for.

Everyone participating in the program is entered already with a "0" so that they all show up on the Reports. From here it's broken down into 2 Queries to Calculate their Monthly Point Totals and the YTD Point Totals. These 2 Queries are the source for the Query I'm trying to create.

Here is what the Query looks like right now...

Surplus Expression: [Points]-([Remainder]*50)
Remainder Expression: CINT([Points]/50)
Has Bonus? Expression: iif([Points]>=50,"Yes","No"
_____________________________________________________________________________
Month |Month1 |Name |Points |YTD Points |Surplus |Remainder |Has Bonus?|

January---1-----Test Guy---24------288---------24---0------------No
February--2-----Test Guy---24------288---------24---0------------No
March----3-----Test Guy---24------288---------24---0------------No
April------4-----Test Guy---24------288--------24----0------------No
May------5-----Test Guy -- 24------288---------24---0------------No
June-----6-----Test Guy----24------288---------24---0------------No
July------7-----Test Guy----24-----288----------24---0------------No
August---8-----Test Guy----24 ----288----------24---0-------------No
September-9---Test Guy----24-----288----------24---0-------------No
October----10--Test Guy----24-----288----------24---0--------------No
November--11---Test Guy---24-----288----------24---0--------------No
December--12---Test Guy--24------288----------24---0--------------No


This is pretty much how I'm trying to get it to look like...
_____________________________________________________________________________
Month|Month1|Name|Points|MTD Points|YTD Points|Surplus|Remainder|Has Bonus? |

January--1--Test Guy--24----24--------288----------0------0---------No
February-2--Test Guy--24----48--------288---------24------0---------No
March---3--Test Guy--24-----72-------288----------22-----1---------Yes
April----4---Test Guy--24-----96-------288---------46-----0-----------No
May----5---Test Guy--24----120-------288---------20-----1----------Yes
June---6---Test Guy--24----144-------288---------44------0----------No
July---7----Test Guy--24----168-------288---------18------1---------Yes
August-8--Test Guy--24-----192-------288---------42------0---------No
September-9-Test Guy--24--216-------288---------16------1---------Yes
October-10--Test Guy--24---240-------288---------40-----0-----------No
November-11-Test Guy--24---264------288---------14-----1----------Yes
December-12-Test Guy--24---288------288---------38-----0----------No

I'd like to know of a way to Calculate the [MTD Points] Field and a way to get an accurate surplus.

Thanks again for any insight you can provide!

Rob