Function Help

[csolomon]

Hello:

I am trying to create a function that will do the following:

1) Every 30 days, check to see if any members of any department have
an incident associated with them

2) If so, the entire department is ineligible for vacation for the
current month as well as the next month

3) If not, the entire department will be awarded 4 hours of leave time
for the new month, added to what they currently have.

For instance:

There was one incident for the stripping crew and none for the loading
crew in March. so the stripping crew would lose leave for April and
May. The next time they would be eligible for leave would be June.
The Loading crew would be awarded 4 leave hours for April.

I have a query that determines employee incidents by department...I am
creating a query that will count to determine if any department has
had an incident...if the count comes back as true then do nothing, but
if it comes back false (meaning no departments have had incidents)
then 4 hours will be added to each employees earned time. The part I
am struggling to understand how to do is the part where we do not
allow an employee to earn leave for two months.

I appreciate any input you can provide.

[ChipR]

You may have to create a table with departments and their current status, signifying allowed or not allowed, which you update when you run this process each month.

[NeoPa]

Unless you want the database to be locked up for 30 days then this cannot be done within a function.

I suggest you need to rethink your requirement.

Have you considered even what will happen should extra leave be awarded?
Where and how it will be stored?

[csolomon]

@NeoPa
What do you mean rethink my requirement? The rules of the system are that departments are evaluated every 30 days. If any department has an incident, all employees of that department are penalized for the next 2 months. these are the requirements of the system that i was given.

I believe if I design my system correctly, extra leave will not be awarded. I have a tblVacationTime that keeps track of employees who have earned vacation Time.

tblVacationTime--Tracks Vacation Earned
vacaID--PK, Number
empID-FK (Employee Table), Number
vacaStartAmt-Number--Leave employee starts with
vacaTimeEarned-Number--leave earned
vacaTimeUsed-Number--Leave Used
vacaAwardDate-Date--Date Leave Awarded

tblVacaHistory--Tracks Vacation History
vacaHistID-PK, Number
vacaID-FK (vacatimeTime)-Number

I just need to figure out how to make it so that if a department has an incident for the next two months they will not be qualified to receive leave. I created a query, empIncidents that tells me incidents for the current month.

SELECT EmpIncidents.empID, Employee.empName, EmpIncidents.incident, Department.dept, Month([incidentDt]) AS M
FROM (Department INNER JOIN Employee ON Department.deptID = Employee.deptID) INNER JOIN EmpIncidents ON Employee.empID = EmpIncidents.empID
WHERE (((Month([incidentDt]))=[Enter Current Month]));

I have another query, Q_PreviousMonthIncidents that finds incidents for the previous month:

SELECT EmpIncidents.empID, Employee.empName, EmpIncidents.incident, Month([incidentDt]) AS M, Department.dept, EmpIncidents.incidentDt
FROM (Department INNER JOIN Employee ON Department.deptID = Employee.deptID) INNER JOIN EmpIncidents ON Employee.empID = EmpIncidents.empID
WHERE (((Month([incidentDt]))=[Enter the Previous Month]));

I am stuck from here. I tried combining the two queries however it only gave me records that were in common, via the keys...

[NeoPa]

Did you not read the whole post? I thought it was explained quite clearly in there. As you've ignored the questions I posed I can only assume not. Please reread the post and let me know if you still have any difficulty understanding the issue explained there (in which case I will endeavour to explain it further).

[csolomon]

@NeoPa
NeoPa,

I appreciate you trying to assist, but often times you come off nasty and I'd just appreciate it if you didn't try if that's your attitude.

when you suggested i rethink my requirements, I took that to mean rethink what the system was supposed to do, which I can not.

I didn't ignore your questions, you only had two:
Have you considered even what will happen should extra leave be awarded?
Where and how it will be stored?

I answered your question about extra leave being awarded...I didn't see how that would occur. If you could explain an instance when or how that might happen, maybe I can then take that into consideration for my design. I even listed where i am storing the leave that will be awarded...so maybe you need to re-read.

I have been working on it and have come up with something but at this point I shutter to post it here so I won't be attacked for having a question

[csolomon]

I see you updated my code...I forgot to do it, thanks, i will get in the habit of doing that.

[OldBirdman]

I am going to restate the problem:
If a department has not had an incident for the last 2 months, all employees in that department will be awarded extra vacation time, to be added to the time they are already entitled to. This will be determined on the first day of the month, and will consider the prior 2 calendar months.
This problem is what to do NOW, based on recorded history. No future involved.

[csolomon]

@OldBirdman

Hi OldBirdman,

Your restatement of the problem is correct except that if a department has not had an incident for 30 days (1 month) all emps in the dept will be awarded vacation time, added to what they already Have acquired. This will be considered on the first day of the new month, and will consider the prior 2 calendar months.

I have a query that I thought worked but it once I added some test dates, it didn't turn out the way I expected. I thought I could determine the incidents from the previous month and compare it with the month before that and combine them, which worked for some instances but not the other. I have no idea what the NOW part is :)

[OldBirdman]

You say that if there is an incident in say December, then no employee will get added vacation in Jan or Feb, per post #4:

The rules of the system are that departments are evaluated every 30 days. If any department has an incident, all employees of that department are penalized for the next 2 months.

Now we are at Feb 1st, and there were no incidents in Jan, so employees get added vacation in Feb, per post #9

... if a department has not had an incident for 30 days (1 month) all emps in the dept will be awarded vacation time

No vacation per post #4, vacation per post #9. I'm going to agree with NeoPa here, who says:

I suggest you need to rethink your requirement.

You are not going to solve a problem until you know the problem you are trying to solve. I was trying to state the problem you were trying to solve, not to solve your problem

I have no idea what the NOW part is :)

In your original post #1, you state:

...the entire department is ineligible for vacation for the current month as well as the next month

This implies that you needed to record data for the future, to be incorporated into the next month's analysis. My restatement only looked to the past, and not the future. NOW is the present.
Once you have defined the problem, you as the analyst/developer must then consider all the details that need to be considered. This may need clarification from management (the persons who proposed this reward/penalty idea). For examples: 1)to quote NeoPa:

what will happen should extra leave be awarded?

2)What happens if an employee changes departments and had an incident in the prior department last month?
Once you have thoroughly analyzed the problem, you can start to design the tables needed. The query designs follow this.

[csolomon]

OldBirdMan,

Thanks for the breakdown...I appreciate the clarification and I see your point. I have inquired from Management things I was uncertain about, and for them, the needs are pretty simple, which is not the case when I sat down to plan it.

In your original post #1, you state:
Quote:
...the entire department is ineligible for vacation for the current month as well as the next month
This implies that you needed to record data for the future, to be incorporated into the next month's analysis. My restatement only looked to the past, and not the future. NOW is the present.
Once you have defined the problem, you as the analyst/developer must then consider all the details that need to be considered. This may need clarification from management (the persons who proposed this reward/penalty idea). For examples: 1)to quote NeoPa:
Quote:

--My apologies. I didn't mean to imply that they were looking to the future to determine whether a team would get leave or not...I was saying that at the beginning of each new month, they look back on the previous month to determine if a dept gets leave or not. I created in my query a field that determined the eligibility date for when the department would be able to receive leave, which was just to add 2 months to the incident month and use that date to determine when a department was eligible again...the issue is like you pointed out, if they violate in March, they are out for April and May, but then in April if they have no incidents does this mean they earn for April...the response I received back was NO, they are still ineligible. I believe that even when a dept does not violate for a month but they are on restriction from a prior month, they do not have the option to earn leave during those months, even if they do not have any incidents.

what will happen should extra leave be awarded?

--I was told there would be no extra leave being awarded.

2)What happens if an employee changes departments and had an incident in the prior department last month?

--I inquired about this and was told that the employee would keep any leave they earned prior to the switch, however they are penalized if the team they transfer to is on restriction at the time.

[csolomon]

Also,

Now we are at Feb 1st, and there were no incidents in Jan, so employees get added vacation in Feb, per post #9
Quote:
... if a department has not had an incident for 30 days (1 month) all emps in the dept will be awarded vacation time

--So it's Feb 1st, and there were no incidents in Jan, all departments except for those under restriction will get an award...even those under restriction who did not have an incident will not be awarded. they are not eligible no matter what until two months after their original infraction.

[OldBirdman]

You miss my points.
1) My restatement does not place departments "under restriction " in the FUTURE. It only looks to the PAST. Does employee XX, in Dept YY get added vacation posted to his record today, Yes or No? If today is Apr 1st, do I add 4 hours of vacation right now to this employee's record? Of course you can do it your way, but I don't think you understand your problem.
2) I said that during analysis you would have to expand the scope of the problem to consider the details not stated here. I gave 2 of perhaps hundreds of little issues that will have to be considered. The fact that you inquired does not eliminate this step, it should make you sit up and take notice.
Until this idea was proposed, there was no need for a program to add bonus vacation to an employee's record. Why shouldn't this program be modified, expanded, or even another program implimented to reward with vacation time?
If an employee has an incident while in another department, what happens to that incident if the employee changes departments. You didn't ask or get an answer to this question, you got an answer to a question you didn't ask. Good, one down, ? to go. To elaborate, employee F in department R has an incident on Aug 31, their last day before transfering to department S. On Sept 1, you run your function, checking each employee's record for incidents. You find F, now in S had an incident Aug 31, so no vacation for any employee in S for Sept or Oct. If that isn't what you want, perhaps your tables are not designed correctly.
I have thought of about 15 questions I would want answers to, that might affect the way I designed the tables, based only on the problem stated in this forum. I'm not going to post them, as I don't want to know the answers. That is your problem.

[csolomon]

1) Does employee XX, in Dept YY get added vacation posted to his record today, Yes or No? If today is Apr 1st, do I add 4 hours of vacation right now to this employee's record?.

--Yes. I actually got two answers; Yes they add it on the first day of the new month and I also got, on the last day of the old month. I'd like to design it so that it works on the last day of the month. But I was also told it didn't matter if it was done on the first day of the new month or the last day of the old month.

2) I said that during analysis you would have to expand the scope of the problem to consider the details not stated here. I gave 2 of perhaps hundreds of little issues that will have to be considered. The fact that you inquired does not eliminate this step, it should make you sit up and take notice.
Until this idea was proposed, there was no need for a program to add bonus vacation to an employee's record. Why shouldn't this program be modified, expanded, or even another program implemented to reward with vacation time?
If an employee has an incident while in another department, what happens to that incident if the employee changes departments. You didn't ask or get an answer to this question, you got an answer to a question you didn't ask. Good, one down, ? to go.

--I have asked several questions to help understand the broad scope of this piece I am making. This is a new incentive program to encourage safety practices and team work. They gave me a short list of rules for this project and despite all of my instances I bought to mgmt's attention, they all have said the same thing. All employees in the violating department will be penalized...even when they switch to a new department.

To elaborate, employee F in department R has an incident on Aug 31, their last day before transferring to department S. On Sept 1, you run your function, checking each employee's record for incidents. You find F, now in S had an incident Aug 31, so no vacation for any employee in S for Sept or Oct. If that isn't what you want, perhaps your tables are not designed correctly

--I suggested that maybe they should check it on the last day of the month as opposed to the first day of the new month. The way it was explained was that when someone switches departments, that individual employee, even though in a new department, will carry over the penalty of their old department. When that penalty from their old dept has expired, they are aligned with their new department's standing.

So to recreate your example:
Employee F in department R has an incident on Aug 31, their last day before transferring to department S. On Sept 1, you run your function, checking each employee's record for incidents. You find F, now in S had an incident Aug 31, so no vacation for any employee in R for Sept or Oct and the F, now in S will have no vacation for Sept or Oct, but the rest of the crew in S will, assuming they didn't have any violations in August.

[Unused01]

I am locked out as OldBirdman, and replying as a new user:
I've tried. I guess I'm not making myself clear. The answers to the various questions affect your Table Design. You can't start with the query. You are not looking at YOUR problem, you are looking at this forum as the problem.
To the question:

1) Does employee XX, in Dept YY get added vacation posted to his record today, Yes or No? If today is Apr 1st, do I add 4 hours of vacation right now to this employee's record?.

you answered:

--Yes

The answer should be "What do I have to check, in which tables, to answer this question for this employee at this time? Do I have that data? Why not?"

--I suggested that maybe they should check it on the last day of the month as opposed to the first day of the new month. The way it was explained was that when someone switches departments, that individual employee, even though in a new department, will carry over the penalty of their old department. When that penalty from their old dept has expired, they are aligned with their new department's standing.

Here again, you miss the point. This is what I mean by using this forum to be the problem. An employee transfers mid-month, 1st, last, or mid-month. Do you know the date of transfer? If you don't, how can you write a query to do the stated problem. I won't (and I can't) ask every question. You need to decide if your tables can reflect a transfer of departments, so the correct departments can be rewarded/punished. Your tables need to be able to handle every condition that can occur in the workplace, such as (but not limited to) transfers, leave-of-absence, sick leave, rehires, change of name (marriage etc.)

[csolomon]

I haven't started developing the tables because I haven't exactly figured out how to track these things. I do however have some existing tables that i knew i'd need to use, so I was just thinking out loud about it, trying to get input (like yours) to help me fully understand what I needed to accomplish this.

The answer should be "What do I have to check, in which tables, to answer this question for this employee at this time? Do I have that data? Why not?"

I thought I had all the data necessary, but this discussion has highlighted the fact that I do not

I didn't think about keeping track of the department transfer history. I didn't take that into consideration...I have some things to clarify and take into consideration

Also, I don't think the forum is the problem, I am grateful for the assistance, I just dont' know as much as some others, so I am in essence learning as I go as well. I dont' always answer the questions correct, but I'm not blaming that on the forum, just inexperience, which I am trying to gain now.

Thanks Again

[NeoPa]

@csolomon
It is posts like this (and some subsequent ones) which indicate to me that you are perhaps not a lost cause, and to give you a bit more leeway.

I hope you have realised by now that my posts were examples of me spending my (volunteer) time to help you better to deal with your problems. That's what this site's about.

If you don't like the tone, then you always have the option not to ask the question in future. I would advise careful consideration though, before criticising those who are trying to help. Their intention may not be to hurt you, but simply to show you where you are doing something in such a way that you will suffer for it. Sometimes that's unpleasant to hear. I appreciate that, but shooting the messenger is not the best solution. Maybe asking the messenger why he seems to be trying to be mean could help.

In this case, OldBirdman has stepped in and I think has demonstrated my points very well and carefully.

I wish you well in your project.

[csolomon]

NeoPa,

I realize that folks are trying to help and I appreciate that; that's why I came here. I didn't really understand your reply; it wasn't clear (to me) as to what you were trying to relay and appeared to be short, so I thought you got frustrated. I apologize for assuming as opposed to asking what you meant.

OldBirdMan helped me to think about other things I hadn't considered which had to be considered for my design. I thought this would be a simple addition to an already created project. I thought all I had to do was have a table to hold information related to my employees earning leave and have a query determine infractions and award based on whoever didn't have a violation...you guys opened my eyes to much more...what that more is I guess will become more clear in the near future (hopefully :))

Thanks.

@NeoPa

[NeoPa]

Nice reply. I appreciate your taking the time to post it.