Originally Posted by
CJ_London
In terms of resources- how many employees? How many auditors? How long does an audit take?
from your comments and just to confirm it is ok for an auditor to audit an employee in the last week of this month and the first week of next month?
if you have it working as required in excel and want to move it to access, you need to be clear on the rules- at the moment it feels more like an idea rather than a thought through process
If you are allowing auditors to 'claim' who they are going to audit then you need 3 tables - employees, auditors and a joining table to contain the FK's to employees and auditors plus a year/month field, a completed field and a timestamp as to when the auditor made the claim.
When claiming the list of available employees needs to exclude as follows:
1. The employees needs 3 audits per months. - exclude those who have a count of 3 for this month (count employeeFK where yearmonth=current year/month)
2. There must at least 5 days between audits. - exclude those whose last audit was less than 5 days from today (timestamp<today-5) ?what about weekends
3. We need to prevent an employee being claimed by more than one auditor. If Auditor 1 claims Employee 1, Auditor 2 can not claim Employee 1 since they've already been claimed. - exclude where timestamp=today
4. We need to prevent the employee that was claimed from appearing back on the claims list for at least 5 days - as 2 above
5. We need a way to manually add the Employee back to the claims list if it's been less than 5 days(the reason is in case Auditor does not complete audit on Employee). - delete the relevant record or modify rules to include where not completed
6. We need a way to manually remove an Employee off the claims list(in case of resignation/termination) - employee record needs a resignation/termination date field - exclude if populated
The 'claim' would create a new record in the joining table