Count backwards 31 days from today excluding dates that meet specific criteria in table

[ui7598h]

This one is way above me. I have a table housing absence data for 98 employees.



I need to build a query that sums attendance "occurrences", counting back 31 calendar days from today, but need to skip the dates of any Excused absence if they appear in the table for each individual. Please see the first example. Today is 4/22/15. I need to count back the 31 days, but skip 4/21, 4/20, 4/17, 4/15, 3/23, 3/17, as they are excused.

I have a query that pulls the absence data based on the criteria of the absence type and assigns an occurrence value to each un-excused absence. it is called QRY_OCCURRENCE. It pulls all the data from the table, and assigns it a value of 1 for more than 4hrs absent and .5 for less than 4 hrs absent.

The second example of the query design is where I am stuck. I can sum the occurrences for the past 31 calendar days, with what I have, but need help getting the date skipping function in place. (the remove from count field is based on a check box for when someone receives an attendance write up.)

the third example is when I run the query. It shows Cynthia with a total of 1.5 occurrences, but she should total 2.5, as 6 of the dates in the table are excused absences.


Please, Any help to get me closer to the answer is greatly appreciated!

[CJ_London]

it might just be a small error in your query - the 'remove from count' column should be Where, not Group By

If this doesn't solve it, please clarify what values are occurence and remove from count - how are they calculated? - I would have thought occurrence would be a count rather than a sum

[CJ_London]

Also, just noticed 3/17 is outside your 31 day limit so you need to add this back - 31 days back from 4/22 is 3/22

[ui7598h]

I guess my explanation was not clear enough. I will break it down a little better.

Employee absences are imported to database from another program. there are several absence codes, of which 5 are considered unexcused under the Attendance policy. they are 1TDF, 1ABU, 1LEF, 1EVA, and 1LRF. If someone receives one of these absence codes, they receive an "occurrence." for absence less than 4 hours, they receive .5 occurrence, and absence greater than 4 hrs they receive 1 full occurrence. if the employee receives 2 occurrences in a 31 day period, they receive disciplinary action. if the employee does not receive another occurrence in the 31 days, the occurrence does not go away. it falls into the second part of the policy that states if they receive 6 occurrences in 182 days, they will receive disciplinary action. once they receive discipline, the "clock starts over," hence the remove from count field. I physically go back and check all boxes for occurrences prior to the date of the discipline.

All other absence codes are considered excused. the verbiage of the attendance policy says that we count backwards 31 or 182 days from today, "exclusive of excused absences." Meaning that any full day absence that is excused, including holidays, are not to be counted in the 31 or 182 day count.

so in the instance above, the 31 day mark is actually about 37 calendar days back, since there are 6 excused absence days within the "true" 31 day window. if I write it out in my own words, it looks like this:

Count backwards 31 days from 4/22/15 but do not count 4/21/15, 4/20/15, 4/17/15, 4/15/15, 3/23/15, 3/17/15 then sum the occurrences between that date and today.

When worked out on a calendar, the 31 day mark goes back to 3/16/15. The employee received 1.5 occurrences in this time period. a full occurrence on 4/14/15 and .5 on 4/9/15. I was mistaken in my calculation of 2.5 in the first post.

Once I have this figured out, I think I will be able to easily transfer it to the 6 occurrences in 182 days.

I have a feeling this is going to be quite an interesting expression once it is all done.

[CJ_London]

OK, it is late here so I'm signing off, but you will need to use a public function to do what you want. Your requirement is a bit different, but the principle is the same as for someone wanting to calculate a delivery date 5 days from today allowing for weekends and bank holidays.

I would google 'calculate working days' or similar to find some threads with the function which you can adapt (basically substitute holidays in their table with your absences from your table, remove weekends?, and work backwards rather than forwards) - here are some examples

http://www.access-programmers.co.uk/...ulate+holidays
http://www.access-programmers.co.uk/...ulate+holidays

[ui7598h]

Thank you. I will definitely check those out.