Show years service

[expublish]

I have a staff database. Within this there is a table that holds employee details. One field is called 'Start Date'.

I need to be able to see a report of what employees have got 5 years service, 10 years service, 15 years serice and 20 years service coming up. I appreciate this are likely to have to be 4 different reports.

However, I don't seem to be able to make a query to pick out those that are within a specific time frame of reaching one of these years' service. Lets say, 10 days.

To summarise:
- I need to make a query to pick out employees that are within 10 days of 5 year service. This need to be based on the start date field, which is in the format dd/mm/yy. I will then base a reprt on this query.

If I can get the 5 year one done, I am pretty sure I can change it to suit 10,15 and 20 years aswell.

All help is appreciated.

Scott.

 

[expublish]

I have worked out how to show how many years service an employee has had using another thread. The SQL looks like this:

SELECT Employees.[Employee Name], Units.[Unit Name], Employees.[Started Work], IIf(DatePart("m",[Started Work])=DatePart("m",Date()) And DatePart("d",[Started Work])>DatePart("d",Date()) Or DatePart("m",[Started Work])>DatePart("m",Date()),DateDiff("yyyy",[Started Work],Date())-1,DateDiff("yyyy",[Started Work],Date())) AS [Years Service]
FROM Units INNER JOIN Employees ON Units.[Unit ID] = Employees.[Unit ID]
ORDER BY IIf(DatePart("m",[Started Work])=DatePart("m",Date()) And DatePart("d",[Started Work])>DatePart("d",Date()) Or DatePart("m",[Started Work])>DatePart("m",Date()),DateDiff("yyyy",[Started Work],Date())-1,DateDiff("yyyy",[Started Work],Date())) DESC;

1) How do I make it show Days, Months and Years - instead of just years?

2) How do I make the query (or a new query based on this one) only show people with a 'service anniversary' coming up in the next 10 days.

Thanks,

Scott.

 

[Geoff Codd]

wouldn't something like this work for you
DateDiff("y",[startdate],(Date()+10))

[This message has been edited by Geoff Codd (edited 04-24-2002).]

 

[expublish]

OK, gave it a try Geoff and that expression shows the number of days each employee has worked.

I still need:

1) How do I make it show Days, Months and Years - instead of the number of days?

2) How do I narrow it down to make it just show people with a '5 year service anniversary' coming up in the next 10 days?

Ideally, the report will show people that have a 5 year service anniversary coming up in the next 10 days.

Thanks,

Scott.

 

[expublish]

After and hour and a half playing around with it i have solved this myself. For future reference:

Step 1: Create a query based on the pervious query (see above). Include all the fields.

Step 2: In the criteria row of the 'Days service' field type:

Between 355 And 365

Step 3: Sort this field descending.

This only works for years worked. It will show all employees that have worked between 355 and 365 days, thus showing all employees with 1 years service approaching in the next 10 days. The 355 and 365 can be changed to the number of years they have worked. (e.g. for 10 years change to Between 3640 And 3650).

If anyone else has a better/alternative way of doing this please feel free to post it.

Oh and if anyone wants to copy this ;-) then the SQL is:

SELECT [days service].[Employee Name], [days service].[Unit Name], [days service].[Started Work], [days service].[Days Service]
FROM [days service]
WHERE ((([days service].[Days Service]) Between 355 And 365))
ORDER BY [days service].[Days Service] DESC;

Scott.

 

[indyaries]

In the query grid, you can enter the below on the Field Line of a blank column:

Year5: [StartDate]+365*5-10

This will yield a date 5 years minus 10 days from the Start Date. You can do 10 years in another field on the query grid thus;

Year10: [StartDate]+365*10-10

...and so forth.

I've tested this. A StartDate of 01/01/2002 results in a 5 Year Anniversary Date of 12/21/2006 (12/31/2006 - 10 Days = 12/21/2006).

Hope this helps.

Bob