CODEWITHSUNDEEP
ms-accessdatabase-designdate-arithmeticcalculation
Nelliel
Nelliel

Reputation: 111

How to calculate employment year based on hire date in MS Access

Thanks for reading this (in advance)..

I have a database that contains a table showing an Employee's Hire Date, and I'm trying to create a PTO table using a Query that calculates Sick Days based on the following criteria:

16 days per working year

If the Hire Date is for eg. 7/7/2016, then an employee has 16 sick days to use between 7/7/2016 and 7/6/2017. At 7/7/2017, the employee once again has 16 days.

I'm trying to come up with the best way to calculate this information where I don't have to manually go in and change the new year for that employee each time he completes a year. (Too many records to manually change)

I considered using the DateAdd() function but it feels extreme or... maybe not the best method to go about doing this. I imagine I have to create a new column to list the new start date for each year (on each employee) but I can't figure out what the BEST calculation would be for it to automatically update each employee per year.

Not to mention, this would only apply to "active" employees as you need to be working for at least 3 months for you to have sick or vacation days....

I've been stressing my head for the past 2 hours looking through all possible formula to find something that I could use... Most of it reverts to me doing something manually each time..

Any ideas are so appreciated (you have no idea).

Upvotes: 0

Views: 1240

Answers (2)

Gustav
Gustav

Reputation: 55816

There is no reason to go via Excel for this.

First, create a query that lists your employees' work years:

SELECT DISTINCT 
    PersonID, 
    DateAdd("yyyy", 10 * Abs([Deca].[id] Mod 10) + Abs([Uno].[id] Mod 10),[HireDate]) AS YearStart, 
    IIf([LeaveDate] >= DateAdd("yyyy", 1, [YearStart]),DateAdd("d", -1, DateAdd("yyyy", 1 ,[YearStart])), [LeaveDate]) AS YearEnd
FROM 
    Personer, 
    MSysObjects AS Uno, 
    MSysObjects AS Deca
WHERE 
    DateAdd("yyyy", 10 * Abs([Deca].[id] Mod 10) + Abs([Uno].[id] Mod 10), [HireDate]) < [LeaveDate];

Save this as qPersonYear. Output will be like:

PersonID    YearStart   YearEnd
05  2000-04-18  2001-04-17
05  2001-04-18  2002-03-31
07  2000-11-01  2001-10-31
07  2001-11-01  2002-10-31
07  2002-11-01  2003-10-31
07  2003-11-01  2004-10-31
07  2004-11-01  2005-10-31
07  2005-11-01  2006-10-31
07  2006-11-01  2007-10-31
07  2007-11-01  2008-10-31
07  2008-11-01  2009-04-30
10  2001-12-01  2002-11-30
10  2002-12-01  2003-11-30
10  2003-12-01  2004-11-30
10  2004-12-01  2005-11-30
10  2005-12-01  2006-11-30
10  2006-12-01  2007-11-30
10  2007-12-01  2008-11-30
10  2008-12-01  2009-11-30
10  2009-12-01  2010-05-31
2   2000-11-01  2001-09-09
8   2001-04-01  2001-10-31

Create another query, qPersonSickDate, to retrieve the sick days.

The only fields needed are PersonID and SickDate.

Finally, create a query to count the sickdays:

SELECT 
    qPersonYear.PersonID, 
    qPersonYear.YearStart, 
    qPersonYear.YearEnd, 
    Count(qPersonSickDate.SickDate) AS SickDays
FROM 
    qPersonYear 
LEFT JOIN 
    qPersonSickDate 
    ON qPersonYear.PersonID = qPersonSickDate.PersonID
WHERE 
    qPersonSickDate.SickDate Between [YearStart] And [YearEnd] 
GROUP BY 
    qPersonYear.PersonID, 
    qPersonYear.YearStart, 
    qPersonYear.YearEnd;

and, voilà, you have your complete and sanitised output:

PersonID    YearStart   YearEnd SickDays
05  2000-04-18  2001-04-17  8
07  2000-11-01  2001-10-31  2
07  2001-11-01  2002-10-31  6
07  2002-11-01  2003-10-31  4
07  2003-11-01  2004-10-31  6
07  2004-11-01  2005-10-31  1
07  2005-11-01  2006-10-31  1
07  2006-11-01  2007-10-31  4
07  2007-11-01  2008-10-31  5
07  2008-11-01  2009-04-30  1
10  2001-12-01  2002-11-30  8
10  2002-12-01  2003-11-30  11
10  2003-12-01  2004-11-30  6
10  2004-12-01  2005-11-30  6
10  2005-12-01  2006-11-30  8
10  2006-12-01  2007-11-30  4
10  2007-12-01  2008-11-30  3
10  2008-12-01  2009-11-30  1
2   2000-11-01  2001-09-09  17
8   2001-04-01  2001-10-31  1

Upvotes: 0

ashleedawg
ashleedawg

Reputation: 21629

Using Excel as an example, put a list of start dates in Column A.

In B1 enter: 

=IF(DATE(YEAR(NOW()),MONTH(A1),DAY(A1))>TODAY(),
    DATE(YEAR(NOW()),MONTH(A1),DAY(A1)),
    DATE(YEAR(NOW()),MONTH(A1),DAY(A1)))

In C1 enter:

=DATE(YEAR(B1)+1,MONTH(A1),DAY(A1))-1

...and fill down the list.

That give you "current employment year" for each employee. Study those formulas so you see how they work, it's actually quite simple.

Those formulas (with slight modifications, like how Access formulas don't start with =) could be used as criteria for an Access query: you want to count how many sick days each employee has used, where:

sick_date  >= (the start of the employment year) AND
sick_date < (the end of the employment year)

Upvotes: 1

Related Questions