Hi Duane,
Thanks for responding. Yes, I have an employees table. Significant Fields are EEID, LastName, FirstName, EEStatus, Company, Hire Date (HDate), RehireDate and LastReviewDate. EEID is autonumber PK. EEStatus is integer. 1 is for Active employees. I don't have a table for employee reviews. I'm mostly looking for how to calculate the effective date. I have a function that I'm currently using. However, I'm not sure it is working properly and I'm wondering if I'm overthinking it. Here is the function:
Public Function EffectiveDate (HDate as Date)
' Calculate effective date for employee reviews
Dim Emonth As Integer
Dim Eday As Integer
Dim EYear As Integer
Dim Rdate As Date
Emonth = IIf(Month(Date) + 1 = Month([HDate]), Month([HDate]), IIf(Month([HDate]) > 6, Month([HDate]) - 6, Month([HDate]) + 6))
EYear = IIf(Month(Date) = "12" And Day(Date) > 15, Year(Date) + 1, Year(Date))
Eday = DatePart("d", [HDate])
Rdate = (DateSerial([EYear], [Emonth], [Eday]))
EffectiveDate = ([Rdate] + (8 - Weekday([Rdate])))
Dim Emonth As Integer
Dim Eday As Integer
Dim EYear As Integer
Dim Rdate As Date
Emonth = IIf(Month(Date) + 1 = Month([HDate]), Month([HDate]), IIf(Month([HDate]) > 6, Month([HDate]) - 6, Month([HDate]) + 6))
EYear = IIf(Month(Date) = "12" And Day(Date) > 15, Year(Date) + 1, Year(Date))
Eday = DatePart("d", [HDate])
Rdate = (DateSerial([EYear], [Emonth], [Eday]))
EffectiveDate = ([Rdate] + (8 - Weekday([Rdate])))
End Function
This function seems to work OK. I'm wondering if there is a better way to calculate the EYear?
Here is the SQL of the query that is the recordsource for the employee review report:
SELECT DatePart("m",IIf(IsNull([re-hiredate]),[Hiredate],[Re-hireDate])) AS RMonth, Employees.[EEID-1], CDate(RHDate([Hiredate],[Re-hireDate])) AS HDATE, CDate(EffectiveDate(RHDate([Hiredate],[Re-hireDate]))) AS EffDate, Employees.LASTNAME, Employees.FIRSTNAME, JobTitles.JOBTITLE, [SFirstName] & " " & [SLastName] AS SupervisorName, Departments.DEPARTMENT
FROM (JobTitles RIGHT JOIN (Departments RIGHT JOIN Employees ON Departments.[DEPARTMENTID-1] = Employees.DEPARTMENTID) ON JobTitles.[JobTitleID-1] = Employees.JobTitleID) LEFT JOIN qrySupervisors ON Employees.SUPERVISORID = qrySupervisors.[EEID-1]
WHERE (((CDate(EffectiveDate(RHDate([Hiredate],[Re-hireDate])))) Between Date()+7 And Date()+14) AND ((Employees.Company)="XPO") AND ((Employees.EEStatus)="1") AND ((Employees.WageType)="H"))
ORDER BY Employees.LASTNAME, Employees.FIRSTNAME;
FROM (JobTitles RIGHT JOIN (Departments RIGHT JOIN Employees ON Departments.[DEPARTMENTID-1] = Employees.DEPARTMENTID) ON JobTitles.[JobTitleID-1] = Employees.JobTitleID) LEFT JOIN qrySupervisors ON Employees.SUPERVISORID = qrySupervisors.[EEID-1]
WHERE (((CDate(EffectiveDate(RHDate([Hiredate],[Re-hireDate])))) Between Date()+7 And Date()+14) AND ((Employees.Company)="XPO") AND ((Employees.EEStatus)="1") AND ((Employees.WageType)="H"))
ORDER BY Employees.LASTNAME, Employees.FIRSTNAME;
I think this is working OK but I'm constantly getting calls that someone is due a review and they didn't get a review report. So I've been tweaking and reviewing to see if I've got something wrong in the process.
Thanks for reviewing this for me.
Doyce
---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :
Hi Doyce,
Can you provide some significant table and field names? Can we assume you have a table of employees and a table of employee reviews?
Regards,
Duane
From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 12, 2018 8:44 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Employee Reviews Date Calculation
Sent: Wednesday, September 12, 2018 8:44 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Employee Reviews Date Calculation
Pros,
We do 2 employee reviews per year based on an employee's hire date. For example, if John was hired on 12/15/2008, in theory, he would get his next 2 reviews on 12/15/2018 and on 6/15/2019. He would also get a raise on the first Monday following a review. Let's call that date the effective date. I want to design a query that will select employee records that are due for a review. I want the records selected between 7 and 14 days. before the review date because I do this once per week and the supervisor's need time to do the review. I also need to calculate the effective date. What is the best approach to do this?
Doyce
__._,_.___
Posted by: winberry.doyce@con-way.com
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Have you tried the highest rated email app?
With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.
SPONSORED LINKS
.
__,_._,___
Tidak ada komentar:
Posting Komentar