Can this be done in a vba function?

[Yam84]

Hello:

I have an idea for a function (or what i think could be a function) and I wanted input as to whether or not it is do-able.

The process says that employees are awarded leave on a monthly basis, if no employee in a certain department has an incident. If an employee does have an incident, that employee along with the other employees in his department are restricted from receiving vacation time/leave for the next two months. Otherwise, they are awarded 4 hours for the new month. this process will be ran once a month, probably on the first day of the new month. The tricky part is dealing with a transferred employee. I need to make sure that if he transfers while his current department is on restriction from receiving leave, that his new dept isn't penalized with him and his old dept.

I have a query that lists emps who have incidents associated with them. My idea was the create a function (EmpRestriction) that:

1)Gets all of the incidents for the last 2 months from the query. In my employee table I have a field, empRestStatus (Restriction Status--holds whether or not the employee is eligible for vacation time/leave).
2)After the query returns the emps with restrictions, set their empRestStatus to True, meaning they have an incident.

The next function I have sets the department restrictions. It should:
1) Get all incidents with in the last 2 months
2) Set their empRestStatus to True
3) Get dept of all emps
**Check to see if the employees have had an infraction within the last 2 months, if they have then set their new department to false (unless a member of their team has an infraction), but ensure the emp who committed the incident is still restricted (I generate an eligibility date for everyone...if they have an infraction a month 2 months after the incident is generated...if not a date for the new month is generated)

The last function I believe I need will add the four hours to anyone who's empRestStatus is False
1) Check infractions from last 2 months
2) If there are none, set empRestStatus to False
3) If status is False, vacaEarnedTime + 4

Is this feasible?

Thank you for your time

 

[KenHigg]

The way I see this is you need to decide if an incident is an attribute of the employee entity or the department entity. I would say it needs to be a department entity attribute. - Do you concur?

 

[Yam84]

Well, I was thinking about that and I am not quite sure. Currently I have an incident table that has empID (PK from employee). If I make it an attribute of department, when an employee transfers, won't the current status of the department affect the employee? For instance if I transfer to dept X from dept Y, will my restriction status go with me? Or can it be adjusted so that I keep the restriction of my previous department, until the restriction is over? If the restriction will carry over to the new department, without penalizing the employees of dept X, then yes, it could be a department entity.

Here is another point worth mentioning: I keep track of the emp transfers, so here is what the table structure looks like:

Employee: empID- pk, empNo, empName
Dept: deptID- pk, Dept
DeptTrans: deptTransID- PK, empID (FK), deptTransDt (date of transfer)

I just grab the most recent transfer date for each employee to determine their latest department, in a query. If I chose to make it an attribute of department, how would I change my table? Add the FK for incident?

Here is my incident table: incidentID (PK), empID (FK), incidentType (FK--there are different types of incidents (3) but only two restrict the employee from receiving leave), incidentDt

 

[KenHigg]

So how about this: Forget making an incident an attribute and make it an entity. You have an incident entity table. It has a pk, some incident type fields, like a description of the incident, the date it happened, etc., then you have a fk to the emplolyee table and a fk to the dept table. Does this sound reasonable so far?

 

[Yam84]

OK...so if I follow you, you are just saying add the FK from the EmpIncidents entity (incidentID) to the employee table and the dept table? Then the relationship would be:

One EmpIncident to Many Employees? Currently it is the other way around, which is how I thought it should be since many one employee will possibly have many incidents associated with them or none at all.

One EmpIncident to Many Departments? I don't currently have incidents associated with depts, but an employee because incidents belong to the employee and not the departments themselves.

 

[KenHigg]

Something like:

tblEmployee
ID (pk)
Name
etc

tblDept
ID (pk)
Name
etc

tblIncident
id (pk)
empID (fk) (One emp can have many incidents)
deptID (fk) (One dept can have many incidents)
description
etc

???

 

[Yam84]

OK...As I said, since I keep a DeptTrans history table, could I just use that ID as opposed to the deptID in the empIncident Table:

tblIncident
IncidentID (pk)
empID (fk) (One emp can have many incidents)
deptTransID (fk) (One dept can have many incidents)
IncidentTypeID
etc

If this table structure is correct, how can I formulate my VBA functions?

 

[KenHigg]

What does the DeptTrans table store and what fields do you have in it?

 

[Yam84]

The DeptTrans table is the table that keeps track of each dept an employee has belonged to. Basically employee transfer history


DeptTrans: deptTransID- PK, empID (FK), deptTransDt (date of transfer)

 

[KenHigg]

I'm thinking you're going to need a table where each record represents specific department.

 

[Yam84]

In my mind, that's what the Incident table is...it tells who made the incident (empID) and from where (deptTransID) and the type of incident, which determines if they are awarded leave or not. I do this in a query. I just needed a way to penalize a whole department, based on the deptID in the table that has had an incident. This is why I was asking about a function.

 

[KenHigg]

Sorry, I don't see any way around a dept table. Maybe I'm missing something and somebody else can jump in - ?

 

[Yam84]

I don't understand. What do you mean about the dept table? I have a dept table:

Employee: empID- pk, empNo, empName
Dept: deptID- pk, Dept

DeptTrans: deptTransID- PK, empID (FK), deptTransDt (date of transfer)