Modified Sum

[redhottsili]

I have made an attendance table for workers which looks like this



Now I need a query which returns the sum of total days attended for each worker. However, there is a caveat. Normally, each worker can work only during weekdays, but if they show up 3 or more days in any given week they would get an extra day credited to them (as if they have also worked on Saturday).
The sums are calculated for each month. So even if the first week of the month starts on a Friday, should the worker have showed up at least 3 days on that week, they will get the extra day on the current month.

[ranman256]

make your count query, qsCountDaysPerEmp, (select empID, count([AttendDate]) from table where [present] = true and dates between start and finish)

now make another query using THAT query, that adds an Extra Day field and a TotalDays ,if the count>3
(select *,iif([countOfDays]>3,1,0) as EarnedDay , [CountOfDays] +iif([countOfDays]>3,1,0) as TotalDays from qsCountDaysPerEmp)

[ranman256]

you really dont need to store dates for a person if they are absent. then the dates owned by the emp are just counted.
if the date doesnt exist, they were absent.

[June7]

I think ranman's suggestion is on the right track. The count query requires input of start and end dates for a single week. This means the query can consider only 1 week of data. If you need output for longer periods, the query will be more complicated.

[redhottsili]

Alright, thank you for the input. I am going to try your suggestion and update.