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
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
Last edited: