Query Syntax

[broodmdh]

I need to write a query to calculate a date (based on other dates) and display it on a form, but I'm having trouble with the syntax. I can write it in T-SQL, but I don't know Access well enough to accomplish it. I'm trying to calculate an anniversary date (20 years, for example) based on a start date and potentially several terms of service (which are added up), discounting any periods where no work took place. For example,

ID EMP_ID START_DATE END_DATE
1 1 1980-01-01 1987-12-31
2 1 1988-01-01 1994-12-31
3 1 1995-01-01 NULL



This employee began work on 1980-01-01 and finished on 1987-12-31 (2921 days). They were then rehired on 1988-01-01 and worked until 1994-12-31 (2556 days). They then began another term of work on 1995-01-01 and have been working since (hence no end date). I need to project an 20-year anniversary (and others) using these dates. Using tsql I would calculate the number of days worked in each term, sum them, and then add them to the original start date. I would then subtract that amount from the 20 years and use the new figure to calculate the anniversary date based on the most recent start date. How do I accomplish this in access? Do I have to use VBA?

[June7]

Maybe queries can accomplish.

You will have to handle the Null - provide current date?

Maybe:

SELECT *, Nz([End_Date], Date()) - [Start_Date] AS ElapsedDays FROM tablename;

Regarding use of Nz() http://allenbrowne.com/QueryPerfIssue.html

Now build an aggregate query using the first query as source to sum the ElapsedDays.

Build another aggregate query that pulls the earliest Start_Date for each employee.
SELECT Emp_ID, Min([Start_Date]) AS FirstHire FROM tablename GROUP BY Emp_ID;

Build another query that joins the two aggregate queries and do calcs for the anniversary date.