How to build a claims list

[Kailef]

Can someone guide me on building a claims list with certain requirements? I'm not even sure if this is possible. I'm new to access.

We have a project going on at work where we are performing audits on employees. We have certain requirements put in place.

We have a list of employees that need audits. We have a list of auditors that do the auditing.

Requirements:
The employees needs 3 audits per months.


There must at least 5 days between audits.

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.
We need to prevent the employee that was claimed from appearing back on the claims list for at least 5 days.
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).
We need a way to manually remove an Employee off the claims list(in case of resignation/termination).

A way to track these claims live as they happen would be beneficial.

[mike60smart]

Hi Kailef

What have you created so far?

[CJ_London]

What about preventing auditors auditing the same employee mot than once per month/period?

what if the employee is on holiday/sick?

same for auditors?

[Kailef]

Hi Kailef

What have you created so far?

On Access, I unfortunately haven't created anything yet. Right now, it's all handled through Excel. It's not ideal with Excel because it's prone to user error and was hoping Access can eliminate a lot of the user error.

I don't even know where to get started with Access.

[Kailef]

What about preventing auditors auditing the same employee mot than once per month/period?

what if the employee is on holiday/sick?

same for auditors?

Yes, we want to prevent auditorss from auditing the same employee more than once per month. Right now, we are doing this through excel with 3 separate worksheets in one workbook on excel. The auditors have the ability to look at worksheet 2 and it will transfer some information from worksheet 1 so that they can see which auditor last audited the employee and on what date.

If the employee or even auditor is on holiday or sick, it doesn't matter. Any work completed can still be audited, even if that employee is on holiday that week. We have the capability of going back the week before and review that work if needed. If the auditor is out sick or on vacation, they're just not working that week and doesn't really affect the employees being claimed and audited. It just means that specific auditor that's out that day/week due to vacation/holiday/sick isn't scheduled to audit that day/week.

[Kailef]

Hi Kailef

What have you created so far?

This is similar to what we need built. The view/edit buttons just allow us to go in and edit the employee's profile, deactivate them if they're terminated/resigned/on leave, etc..and then the empty one where there is no claim button basically means that agent has been claimed, and we can also go in and unclaim them if need be.

[mike60smart]

can you upload a copy of the spreadsheet?

[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

[Kailef]

can you upload a copy of the spreadsheet?

This is similar to how our current auditing procedure is set up. It's not ideal because of the potential for user error. Some of the columns are locked so they can't shift the data. If an auditor isn't paying attention though, they may audit employee 1 and then accidentally put it on the next row and say they audited employee 2. Unfortunately, it doesn't allow to upload the excel sheet unless I zip it. I created a blank copy due to protect the privacy of the employees and such, but these screenshots are how the current excel sheet is set up.

We need a way to build a claims list so that agents become unavailable for other auditors once another auditor claims an employee.

[Kailef]

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

The number of employees frequently change on a weekly, sometimes daily basis. As of right now, we're talking close to 600 employees with that number increasing close to 1000 by December.
An audit usually takes 15-20 minutes. It can depend on the workload required.
As for when it's okay to audit the employee, we usually audit them early on during the first week. If the employee is there throughout the entire month, then 3 audits for the entire month are required, but must be at least 5 days between audits. Most times, they tend to go 8-10 days between audits because we have a lot of employees that need audited.

It's not an idea. We're looking for a better solution because Excel is not ideal for what we're trying to do because it's just prone to way too much user error.

As for the tables, yes, I was thinking 3 tables. One to consist of just the employees since that roster can and does change daily/weekly. We need a 2nd table just for the auditors. Then a 3rd table that allows for the addition of other needed data such as the date they are audited, the ID of the task audited, and then a relationship to bond them all together so that it creates the data being displayed.

When building a claims list, several things need to be factored in, and one thing to keep in mind is that we do not care about the last audit date at the end of the previous month. So even if an employee is audited on Nov 30th, they can be audited again on December 1st. We have not been caring about implementing that when it's a new month. If we can implement that too, then great, but if not it's no big deal.
1. Has the agent been audiited in the last 5 days?
1a) if audited within the last 5 days, do not appear on the claims list.
1b) If not audited within the last 5 days, appear on the claims list.

2. How many audits does the employee have?
2a) If 3, employee no longer appears on the list.
2b) If less than 3, then employee appears on the list as long as the last audit date is 5 days or more.

3) Has the employee been claimed today?
3a) If so, the employee does not appear on the list.
3b) If not, the employee is on the list.

4) Manual way to readd employee for when auditor claims employee and does not finish the audit. This will allow another auditor to claim and audit them the next day.

5) Manual way to remove an employee for when resignation/termination occurs.

Once an audit claims and audits the agent, the ID, audit date, etc...appear on the new data table that will show all audits.

I was thinking once the employee is claimed, a form pops up for the auditor to fill out where they can input what task ID to include and the date they audit them.

[mike60smart]

Hi
If an Auditor1 carries out an Audit on Employee1

After 5 days can Auditor1 carry out another audit on Employee1 or must the audit
be carried out by another Auditor?

[mike60smart]

Do you have a List of Tasks that the Auditor uses? Can you give us some examples?

[Kailef]

Hi
If an Auditor1 carries out an Audit on Employee1

After 5 days can Auditor1 carry out another audit on Employee1 or must the audit
be carried out by another Auditor?

Sorry for not responding sooner. I have been under the weather.

After 5 days, Auditor1 can carry out another audit on Employee1. We would prefer if Auditor2 does, but in case of an auditor shortage, we need to have the option so auditor1 can if needed. If we have enough auditors, then Auditor1 just knows now to claim Employee1.

[Kailef]

Do you have a List of Tasks that the Auditor uses? Can you give us some examples?

Here's an example. I had to mark out names for security reasons. The first date is the date when the task took place. The second date is when the auditor reviewed the task.

[mike60smart]

So the Employee carries out a number of Tasks on specific Dates and then an Auditor audits those tasks