Re: [MS_AccessPros] Employee Reviews Date Calculation

 

Doyce,

It would be really simple if you looked at the LastReviewDate to find out if it was between  X and Y number of days ago. 

Regards,

Duane

---

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of winberry.doyce@con-way.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com>
Sent: Wednesday, September 12, 2018 12:25 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Employee Reviews Date Calculation

 

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]))) 

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;

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

 

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: Duane Hookom <duanehookom@hotmail.com>

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (4)

---

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.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___