Can any one help me with creating codes that will calculate how many days an employee is getting pay per [u]fiscal year</u> and at what level? My fiscal year starts on July 1st and ends on June 30th the following year.

For example Persno 11527 would get pay for:

fiscal year 2005 (7/1/2004 to 6/30/2005) at
Level 1 from 11/8/2004 to 5/1/2005 plus
Level 2 from 5/2/2005 to 6/30/2005


fiscal year 2006 (7/1/2005 to 6/30/2006) at
Level 2 from 7/1/2005 to 04/26/2006 plus
Level 3 from 4/27/2006 to 6/30/2006


fiscal year 2007 (7/1/2006 to 6/30/2007) at
Level 3 from 7/1/2006 to 4/21/2007 plus
Level 4 from 4/22/2007 to 6/30/2007


fiscal year 2008 (7/1/2007 to 6/30/2008) at
Level 4 from 7/1/2007 to 4/15/2008 plus
Level 5 from 7/16/2008 to 6/30/2008


For records with no enddate the enddate is assumed to be equalled to 12/31/9999.


Here's a sample of my records. Persno, PSGroup, Level, Daily Rate, StartDate, and EndDate.


Persno PSGroup Level Daily Rate StartDate EndDate
11527 B420 1 $138.44 11/8/2004 05/01/2005
11527 B420 2 $145.12 5/2/2005 04/26/2006
11527 B420 3 $152.06 4/27/2006 04/21/2007
11527 B420 4 $159.44 4/22/2007 04/15/2008
11527 B420 5 $167.16 4/16/2008
11518 B466 1 $172.00 10/4/2004 04/01/2005
11518 B466 2 $180.25 4/2/2005 03/27/2006
11518 B466 3 $188.95 3/28/2006 03/22/2007
11518 B466 4 $198.17 3/23/2007 03/16/2008
11518 B466 5 $207.92 3/17/2008
11503 B564 1 $273.98 8/23/2004 02/01/2005
11503 B564 2 $287.42 2/2/2005 01/27/2006
11503 B564 3 $301.65 1/28/2006 01/22/2007
11503 B564 4 $316.50 1/23/2007 01/17/2008
11503 B564 5 $332.13 1/18/2008
11489 R34 $513.46 8/9/2004
11483 R40 $673.08 8/2/2004
11481 R32 $446.15 7/26/2004
11470 R29 $384.42 7/19/2004
11454 U75 1 $275.04 11/1/2004 07/01/2005
11454 U75 2 $293.84 7/2/2005 06/26/2006
11453 B543 1 $247.89 7/6/2004 01/01/2005
11453 B543 2 $260.01 1/2/2005 12/27/2005
11453 B543 3 $272.66 12/28/2005 12/22/2006
11453 B543 4 $286.10 12/23/2006 12/17/2007
11453 B543 5 $300.16 12/18/2007
11451 B543 1 $247.89 6/21/2004 12/01/2004
11451 B543 2 $260.01 12/2/2004 11/26/2005
11451 B543 3 $272.66 11/27/2005 11/21/2006
11451 B543 4 $286.10 11/22/2006 11/16/2007
11451 B543 5 $300.16 11/17/2007
11436 R32 $461.54 6/7/2004
11398 B517 2 $229.80 9/27/2004 09/01/2005
11398 B517 3 $240.95 9/2/2005 08/27/2006
11398 B517 4 $252.72 8/28/2006 08/22/2007
11397 B517 2 $229.80 9/27/2004 09/01/2005
11397 B517 3 $240.95 9/2/2005 08/27/2006
11397 B517 4 $252.72 8/28/2006 08/22/2007
11396 B517 2 $229.80 9/27/2004 09/01/2005
11396 B517 3 $240.95 9/2/2005 08/27/2006
11396 B517 4 $252.72 8/28/2006 08/22/2007


Thank you,


Vann

Here's the way I would do it.

First I would create a query with the following calculated field:
1) Showing the fiscal year for the StartDate i.e. FisYear: IIf(Month(StartDate)< 7, Year(StartDate), Year(StartDate) + 1)

2)A field that shows the number of days from start date to end date i.e. DaysOfWork: DateDiff("D", EndDate, StartDate). Check the DateDiff function to make sure I gave you the right syntax.

3) Then I would check if the date range crossed fiscal year borders and do math accordingly: FisYr1:IIf(StartDate < DateSerial(FisYear, 7, 1) And EndDate >= DateSerial(FisYear, 7, 1), DateDiff("d", DateSerial(FisYear, 7, 1), Startdate), DaysOfWork)

4) Finally if DaysOfWork is greater than FisYr1 then get the difference and put it into FisYr2. Otherwise FisYr2 is 0. FisYr2: IIf(DaysOfWork > FisYr1, DaysOfWork - FisYr1, 0)

5) I forgot to include that you'll need to know the Year for FisYear2: FisYear: IIf(Month(StartDate)< 7, Year(StartDate + 1), Year(StartDate) + 2)


That will Break should break the year up for you.

After that I think you're going to have to write some VBA code to insert FisYr1 and FisYr2 into two different records.

Once that's done you should be able to run a totals query to aggregate the rest.

This is off the top, so there might be some logic or syntax errors but it's worth a shot.

Let me know how it goes.
ea

Can a Level span more than two fiscal years?

Clive Astley

Hi EA,

Thank you. I'll work on it this weekend & I'll let you know how it works out. Again, thanks!

Vann

Hi Clive,

Yes, once an employee reaches the maximum level, either at 3, 5 or 7 then he/she will stay at that level for the duration of his/her employment.

Before an employee reaches the maximum level, his/her pay will cross fiscal year due to the time it takes to move from one level to the next either six months or one year.

Thank you,


Vann