Find the date of Anniversary 2

[striker83716]

Hi All. I have a database that I need help with and am not sure how to get the answer I need. 2 tables with a 1 to many relationship
TblEmployees
pk IDEmployee
BadgeNumber
LName
FName
TBLEmployment (Qry based on this table shows duration)
pk IDEmployment
fk idEmployee
StartDate
EndDate

An employee may have been hired/layed off several times. What I would like to determine is the date a employee reaches his 5 year Anniversary based upon the date ranges he was employed. A example (not going to mess with Leap year)

start date end date duration
01/01/1990 06/25/1992 906
09/27/1995 10/29/2004 3320

What would be the 5 year Anniversary date or in other words the 1825th day worked? which should be 04/03/1998

 

[smedvid]

Can't u simply use DateAdd() and Add 5 Years?

Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[jdegeorge]

Use an expression in your query

5th Anniversary: [StartDate]-[PreviousDuration]+1825

Hope this helps.

Jim DeGeorge

 

[jdegeorge]

Smedvid

That would give him a 5th anniversary in 2000. You have to technically change the re-hire date to a date that is 906 days earlier to account for the previous duration period.

Jim DeGeorge

 

[striker83716]

jdegeorge,
5th Anniversary: [StartDate]-[PreviousDuration]+1825. What populates "PreviousDuration

 

[jdegeorge]

It would be the duration determined for the first employement period. Granted, with multiple records in tblEmployment this won't be easy to do. I was just suggesting a concept.

Can your employees leave and be re-hired more than once? If not, then have only 1 record for an employee. Change StartDate and EndDate to CurrentStartDate and CurrentEndDate. Add 2 new fields for PreviousStartDate and PreviousEndDate.

Then, your formula would be:

[CurrentStartDate]-([PreviousEndDate]-[PreviousStartDate])+1825

Hope this helps.


Jim DeGeorge

 

[striker83716]

oooh that could get ugly , some employees may have been and may be layed-off several times, so putting it all in one record would not be a very practical solution.

 

[jdegeorge]

Yep, that would get sticky.

Then, going back to the original suggestion, you have to determine the duration for each employment period, then subtract that from the most recent start date and then add 1825 to it. That'll give you the retro-5th anniversary.

I'm going to try to apply this theory to a dummy table myself right now. I've got an employee with 2 former and 1 current employment periods just to try it out.

I'll post the solution when I get it.

Did I confuse you any?

Jim DeGeorge

 

[striker83716]

Not confused yet, I look forward to the solution you come up with.

Rob

 

[jdegeorge]

Got it!

You need 2 queries.

Here's the SQL for the first query:

SELECT tblEmployment.idEmployee, Sum(([EndDate]-[StartDate])) AS Duration
FROM tblEmployment
GROUP BY tblEmployment.idEmployee;

Then base the 2nd query on that:

SELECT tblEmployment.idEmployee, tblEmployment.StartDate, tblEmployment.EndDate, Query1.Duration, [StartDate]-[Duration]+1825 AS [5th Anniversary]
FROM tblEmployment INNER JOIN Query1 ON tblEmployment.idEmployee = Query1.idEmployee
WHERE (((tblEmployment.EndDate) Is Null));

Open a new query, change to SQL view, and paste in the first set of code. Save it and call it QUERY1. You can use a different name, but change QUERY1 in the code to whatever the query is called.

Open a new 2nd query and do the same with the 2nd set of code.

Good luck!



Jim DeGeorge

 

[jdegeorge]

Hold on! That doesn't work for an employee who's only been hired once and still working!

Give me a few more minutes.

Jim DeGeorge

 

[jdegeorge]

Open the 2nd query and change to SQL mode.

Insert this:

SELECT tblEmployment.idEmployee, tblEmployment.StartDate, tblEmployment.EndDate, Query1.Duration, [StartDate]-IIf(IsNull([Duration]),0,[Duration])+1825 AS [5th Anniversary]
FROM tblEmployment INNER JOIN Query1 ON tblEmployment.idEmployee = Query1.idEmployee
WHERE (((tblEmployment.EndDate) Is Null));

The problem is that when the employee is current and has never been previously employed, Duration is NULL not 0, so the formula won't work.

This replaces a NULL Duration with 0 and then it works.

Of course, now you're back to your problem of determining leap year values!

No idea on how to do that by adding an assumed 365 days per year as you're doing.

Good luck!

Jim DeGeorge

 

[striker83716]

Thank You Jim, You have gotten me pretty close.. It works well if the employee's initial duration is less than 1825. I believe a simple "if" statement may get it to work for all employees

 

[striker83716]

Let me say thank you again. You must of caught that while I was typing.

Rob

 

[jdegeorge]

This eliminates the need to worry about leap year.

Again, paste this in place of the 2nd queries SQL:

SELECT tblEmployment.idEmployee, tblEmployment.StartDate, tblEmployment.EndDate, Query1.Duration, Month([StartDate]) & "/" & Day([StartDate]) & "/" & Year([StartDate]-IIf(IsNull([Duration]),0,[Duration]))+5 AS [5th Anniversary]
FROM tblEmployment INNER JOIN Query1 ON tblEmployment.idEmployee = Query1.idEmployee
WHERE (((tblEmployment.EndDate) Is Null));

What it does is get you 1825 days after the start date or retro-start date, then just appends the MONTH and DAY from the StartDate and adds the 5th YEAR.

Jim DeGeorge

 

[striker83716]

Still have a scenario where the code needs modified. If the initial employment of an employee is greater than 5 years. then he was laid off for a while, and now works for us again for a long duration I get the incorrect date. such as
StartDate EndDate Duration
8/15/77 6/17/83 2132
6/24/87 6337
This scenario gives me an answer of 6/24/1989, but should have been 8/15/82. any further solutions?

 

[jdegeorge]

Progam logic aside, if you're giving people credit for past time worked towards benefits and anniversary, and the resulting "anniversary" date is in the past, do you really need to know what the 5th anniversary was if it's past?

We can most likely work through this one with another IIF statement somewhere, but before I bother with that I just wanted to know.

Jim DeGeorge

 

[striker83716]

You are probably right there since it has happened in the past, however in addition to the 5 year anniversary I am modifying the query to show the 1,3,5,10,15 & 20 year anniversaries. This is going to then be used to distribute to the employees for their evaluation as we transistion from benefits based upon their anniversary dates to a standard calander basis. We feel there will be some resistance, and any errors in the reporting may cause unfavorable results.

 

[jdegeorge]

Hold the presses!!! I retract that last statement.

The math is the math. The person originally worked for over 5 years then left. He/she was rehired more than b years ago.

In this last example, the 5th year anniversary was in 06/24/1986, still in the past.

The formula works.

Jim DeGeorge

 

[striker83716]

Only 2 problems with that. 1st he did not work for us on 6/24/1986 and secondly 5 years employment with us would have been 8/15/82