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

 

[jdegeorge]

Striker

Based solely on his original employment period, he already had his 5th anniversary in 1982.

Based purely on the formula, where you subract the original duration from the new start date, his 5th year anniversary is 1986.

There's no logic in the formula that evaluates the original duration to know that the anniversary already passed.

We could probably change it so that the IIF statement is nested, such that there are several possible values for DURATION.

But, I repeat my question from before...if they've already had a 5th anniversary and it was in the past, or if the formula results in a 5th anniversary that is in the past, what's the benefit of knowing that date?

Jim DeGeorge

 

[PHV]

Variation on the theme:
SELECT E.idEmployee, DateAdd("yyyy", 5, E.StartDate-Nz(S.Duration,0)) AS [Coming 5th Anniversary]
FROM tblEmployment E INNER JOIN (
SELECT idEmployee, Sum(EndDate-StartDate) AS Duration
FROM tblEmployment GROUP BY idEmployee
) S ON E.idEmployee = S.idEmployee
WHERE E.EndDate Is Null And DateAdd("yyyy", 5, E.StartDate-Nz(S.Duration,0))>=Date()
;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

Let me state this question another way.
I want to know the 1825th day of these ranges if they were all related to 1 person, starting with the earliest range.

StartDate EndDate
8/15/77 2/19/78
3/31/80 9/19/82
4/24/83 6/25/89

The result in this case would be 4/28/85

StartDate EndDate
8/15/77 <--- null still working

This would result in 8/15/82

Any further ideas??? Maybe I need to write some type of code, rather than a query and my post is in the wrong area.

 

[PHV]

And what about something like this ?
SELECT E.idEmployee, E.StartDate, E.EndDate, (
SELECT Sum(D.EndDate-D.StartDate) FROM tblEmployment D
WHERE D.idEmployee=E.idEmployee AND D.EndDate<E.EndDate
) AS Duration, DateAdd("yyyy", 5, E.StartDate-Nz(Duration,0)) AS [5th Anniversary]
FROM tblEmployment E
WHERE [5th Anniversary]>=E.StartDate
And (E.EndDate Is Null Or [5th Anniversary]<=E.EndDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

PH,
tblEmployee does not have any dates. all dates are in the tblEmployment

 

[PHV]

I only use tblEmployment !

 

[striker83716]

PH,
If I paste the sql statement you suggested I get a "Enter Parameter Value dialog box asking for the "5th Anniversary" Can you tell me what I am doing wrong? I really appreciate your help.

 

[PHV]

OK, try this:
SELECT E.idEmployee, E.StartDate, E.EndDate, (
SELECT Sum(D.EndDate-D.StartDate) FROM tblEmployment D
WHERE D.idEmployee=E.idEmployee AND D.EndDate<E.EndDate
) AS Duration, DateAdd("yyyy", 5, E.StartDate-Nz(Duration,0)) AS [5th Anniversary]
FROM tblEmployment E
WHERE DateAdd("yyyy", 5, E.StartDate-Nz(Duration,0))>=E.StartDate
And (E.EndDate Is Null Or DateAdd("yyyy", 5, E.StartDate-Nz(Duration,0))<=E.EndDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

here I get "Enter Parameter Value dialog box asking for the "Duration"? sorry to be such a pain but I am really struggling here.

 

[PHV]

Which version of access ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

2003, happy to convert If i need to. If so tell me which version you have.

 

[PHV]

Create a saved query, say qryDuration:
SELECT E.idEmployee, E.StartDate, E.EndDate, (
SELECT Sum(D.EndDate-D.StartDate) FROM tblEmployment D
WHERE D.idEmployee=E.idEmployee AND D.EndDate<E.EndDate
) AS Duration
FROM tblEmployment E

Test it and if OK, try this:
SELECT idEmployee, StartDate, EndDate, Duration
, DateAdd("yyyy", 5, StartDate-Nz(Duration,0)) AS [5th Anniversary]
FROM qryDuration
WHERE DateAdd("yyyy", 5, StartDate-Nz(Duration,0))>=StartDate
And (EndDate Is Null Or DateAdd("yyyy", 5, StartDate-Nz(Duration,0))<=EndDate)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[striker83716]

Woo-Hoo it works. Thank you So much for your help. It does give me more then one record for each employee but I can easily create a "min" on the date column. Thanks again for your help.

Rob

 

[PHV]

Yeah, I can't test today as I don't have access at hand for the moement ...
Glad you solved your issue and thanks for the pinkie.
the query to show the 1,3,5,10,15 & 20 year anniversaries
Simply modify the three DateAdd calls.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244