Anniversary Dates

[tahuero]

I have a simple database that stores dates based on hire date. How do I
pull only employees that will have an anniversay during a month based
on specific criteria (i.e... employees with 5 years, 10 years 15 years
and so forth)?"

 

[Allen Browne]

Create a table with just one field named CountID, of type Number.
Mark the field as primary key.
Save the table as (say) tblCountID.
Enter the records for the years you want to be reminded of, e.g.:
5
10
15
...

Create a query that uses your Employee table and tblCount. There must be no
line joining the 2 queries in the upper pane of query design. It is the lack
of any join (known as a Cartesian product) that gives you every possible
combination.

In this query, type an expression like this into a fresh column in the Field
row:
Anniversary: DateAdd("yyyy", [tblCount].[CountID],
[Employee].[StartDate])

 

[John Spencer]

If your anniversary years are all divisible by 5 then you could use criteria
like the following.

WHERE (Year(Date()) - Year(HireDate)) MOD 5 = 0 and Year(Date()) <>
Year(HireDate) AND Month(Date()) = Month(HireDate)

In the query grid that would look like:

Field: YearHired: Year(HireDate)
Criteria: <> Year(Date())

Field: MonthHired: Month(HireDate)
Criteria: Month(Date())

Field: FifthYear: (Year(Date()) - Year(HireDate)) MOD 5
Criteria: 0

 

[tahuero]

Thanks a bunch. I have used your tips a great deal and once again you
have come through. I had to tweak just a bit to customize to company's
report, but you gave me the foundation