Monthly automatic calculations

[Phoebe]

Hello Experts,

I have been beating my head against a wall trying to figure out the best way to do the following.

I have a table containing "points" that people accrue for late arrival, not working a full day, etc.

If an individual has perfect attendance for a month, they automatically drop a point.

I am not by any means an Access expert and cannot figure out a simple and foolproof way to accomplish this, so any suggestions would be greatly appreciated.

Many thanks in advance.

 

[G37Sam]

You can have a Scheduled Task in windows to open your access DB every month.

Once your DB opens, you can have it run a certain query/module depending on your exact needs.

 

[Phoebe]

Great, thanks, that helps!

Any suggestions on how to perform the actual calculations? I know I have to add all the points and do an If Then, but I am a little fuzzy on the actual logic.

 

[robina]

The easiest way would be to create your query and in design view click the "totals" on the design ribbon. In the [points] field Instead of "group by" choose "sum". this will give you your total points. To get the dates you need, you would have a date field in the query as well. In the criteria area you would type BETWEEN [beginning date] AND [End date]. Now when the query is run it will prompt you for the dates you wants to see.

 

[G37Sam]

It always good to break down the problem.

First, you'd have to define what you exactly mean by perfect attendance. Is that 9 hours/day x 5 days x 4 weeks = 180 hours/month? If it's that simple, then you can sum the attended hours and compare it with 180. If you want to account for public holidays/sick days then you will need to either hard code that or just create a table.

I developed an attendance management DB a while back and there were so many exceptions to the dates that I ended up creating a table for all the days in the semester where attendance was expected and compared against it.