|
 |
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
|
 |
|
|
 |

December 3rd, 2004, 12:08 PM
|
Registered User
|
|
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Calculating number of days between multiple dates
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
|

December 3rd, 2004, 02:02 PM
|
Registered User
|
|
Join Date: Nov 2004
Location: New York, NY, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

December 3rd, 2004, 02:29 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Location: Norwich, Norfolk, United Kingdom.
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Can a Level span more than two fiscal years?
Clive Astley
|

December 3rd, 2004, 08:20 PM
|
Registered User
|
|
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by ea
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
|
Hi EA,
Thank you. I'll work on it this weekend & I'll let you know how it works out. Again, thanks!
Vann
|

December 3rd, 2004, 08:26 PM
|
Registered User
|
|
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Quote:
quote:Originally posted by Clive Astley
Can a Level span more than two fiscal years?
Clive Astley
|
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
|
Thread Tools |
Search this Thread |
|
|
Display Modes |
Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
 |