Auto Delete Number Constant After Certain Time Frame

[Mearntain]

I have an access database with one central Table with all of the data in it. There are 5 columns - Employee, Supervisor, Date, Points Assessed, & Comments.



How points work - If you go 90 days from your last violation with no points, 3 points are deducted from your balance. If 90 additional days pass, 3 more points come off. This will continue to happen until you reach -3 points (Negative 3 points).

What I am trying to do is figure out the best way to have the points auto-deducted from the total points. Currently, total points are calculated using a query but I do not have a method for deleting the proper number of points yet. I'm thinking to get a solution to my issue, I may need to make a new table to display the total points, and then have the 3 point deductions taken out of the new secondary table; however, I am unsure of how to make this fully work.

What would be the best way to make this work out properly? If i didn't make things clear enough, let me know what you would like me to elaborate on. Thanks in advance for any help you may be able to offer.


**EDIT** I am using Access 2013, not 2003 as it says in my signature. I updated this info on my profile but it did not update it on the post.

[ranman256]

you have a query, qs90People, that pulls all folks with criteria: DateAdd("d",90,[date]) =Date()
every day you run an update query that uses this list and if any are in it, it updates the Date field to Date() and subtracts 3 from Points.

[ssanfu]

Does this "one central Table" only concern violations?
(I see there is not a field for violation type.)


My thoughts....... FWIW

Lets say the table has no records.
Then Bob (poor Bob) has a brain fart and gets a 6 point violation on 15 Jan 2016.
On 22 Jan 2016, Bob has another brain bubble and gets another 6 point violation. (total 12 points).

Time goes by and Bob doesn't have any more violations.
So on 22 Apr 2016 (after 90 days), a record is entered for Bob with -3 points. (total is now 9 points)

More time goes by (another 3 months)..... Bob is still on good behavior..
On 22 July 2016, a record is entered for Bob with -3 points. (total is now 6 points)

On 10 Aug 2016, Bob gets rebellious and gets a violation of 2 points (total is now 8 points)

Now Bob has to be good until 10 Nov 2016 to get points removed??



I would use VBA code to add the 3 points deduction records.

Use a totals query; return anyone with positive points assessed (or greater than -3 points?).
Get the date of the latest points record (positive or negative).
Compare that latest date + 90 days to the current date.
If latest date + 90 days = current date, add a record of -3 points.

Do this for each person in the totals query.

---------------------------------
Pseudo code for how I would start writing the code.


- Does the 90 days include weekends or only work days?
-Not sure how this would run "automatically".

[Mearntain]

Does this "one central Table" only concern violations?
(I see there is not a field for violation type.)

Yes, this table only concerns violations. Comments is where the type of violation would go to.

Lets say the table has no records.
Then Bob (poor Bob) has a brain fart and gets a 6 point violation on 15 Jan 2016.
On 22 Jan 2016, Bob has another brain bubble and gets another 6 point violation. (total 12 points).

Time goes by and Bob doesn't have any more violations.
So on 22 Apr 2016 (after 90 days), a record is entered for Bob with -3 points. (total is now 9 points)

More time goes by (another 3 months)..... Bob is still on good behavior..
On 22 July 2016, a record is entered for Bob with -3 points. (total is now 6 points)

On 10 Aug 2016, Bob gets rebellious and gets a violation of 2 points (total is now 8 points)

Now Bob has to be good until 10 Nov 2016 to get points removed??

Yes you are correct, that is exactly what I am trying to achieve.

I would use VBA code to add the 3 points deduction records.

Use a totals query; return anyone with positive points assessed (or greater than -3 points?).
Get the date of the latest points record (positive or negative).
Compare that latest date + 90 days to the current date.
If latest date + 90 days = current date, add a record of -3 points.

Do this for each person in the totals query.

---------------------------------
Pseudo code for how I would start writing the code.


- Does the 90 days include weekends or only work days?
-Not sure how this would run "automatically".

The 90 days are calendar days and will include weekends and holidays as well.

I can tie the code into a button on one of the forms and have it ran every time someone submits a record.

Would you be able to assist me with getting the VBA right? I know a little bit but am no pro by any means on the VBA side.

[Mearntain]

you have a query, qs90People, that pulls all folks with criteria: DateAdd("d",90,[date]) =Date()
every day you run an update query that uses this list and if any are in it, it updates the Date field to Date() and subtracts 3 from Points.

I will give that shot with the update quries and see how that works out. Thanks

[ssanfu]

I can tie the code into a button on one of the forms and have it ran every time someone submits a record.

What happens if a record is not entered for 5 days and someone was supposed to get -3 2 days ago? It can be programmed for this issue, but you (the programmer) have to account for it.


Yes, I would be able to help you with the code...

[Mearntain]

What happens if a record is not entered for 5 days and someone was supposed to get -3 2 days ago? It can be programmed for this issue, but you (the programmer) have to account for it.


Yes, I would be able to help you with the code...

I think everything should work out fine if I tie it in to the button I was talking about. The reason I say this is because you would be pressing the button before running the report, which would make the correct points be reflected in the table by the time the report was printed out. As long as the date for the "new record" of -3 points that the code placed in the table reflects that of the maxofthedate (date of last violation) PLUS 90 days, everything should work fine. Ill plan on running the code as soon as the button is pressed, but before the other record is submitted.

MaxOftheDate can be found via a query I already have in a place named "Report Query A"

[ssanfu]

Sounds good. I'll monitor this thread in case you have questions about the code....

Good luck.....

[Mearntain]

To be honest, I don't even know where to begin with writing the code to search the table and write the new records. I've been playing around some with update queries, etc. but haven't had any luck getting it to work yet. I've got it 80% of the way working so far but its still not doing everything I want it to.

[ssanfu]

You want to post your dB so we can take a look at what you have?

Do a "Compact & Repair", then zip it.

[Mearntain]

Not sure how to post it directly to the site, but here's a copy of it on box.com

http://app.box.com/s/hg5f2yohvskd65hejhwsgybo68uvn2f1

[Mearntain]

Bump bump .

[Homegrownandy]

click go advanced underneth the box where you post to add attachments.

[ssanfu]

Sorry... got really busy with work, snow and then warming....

I added a "search" (actually it is a filter) form and added the code to add point deductions (-3 points). There are two new buttons on the main form.


I'm not crazy about the object names (special characters and LOTS of spaces - both bad ideas).

See if this helps.....

[Mearntain]

Sorry for the delay getting back to here, I didn't realize a reply had come through. From what I can see, everything seems to be working as planned. I will play around with it some and report back if anything isn't. Is there an easy way to add to the code to have the Supervisor Name be added when the -3 point records are added? Im sure it is, I just dont know how to do it myself. I know how to make it happen with an update query, but was thinking there may be an easier way if it was just added in with the code. Thanks again for the help, I really appreciate it.