Weekly to Monthly Conversion 1

[newguy86]

I'm working in an Access 2007 db and I have a table that stores forecasted hours by project by week-ending date (Fridays). What i'm working on is a report that shows those forecasted hours by project by month.

The problem that I'm having is that some weeks are split between 2 months and they want those forecasted hours to be represented in their respective months. For example the first week of February has 2 days in January and 3 days in February and they want those numbers to show up in the month that they're being utilized in.

Here's the logic that I have worked out so far:
If Day([WEDate]) < 5 Then
Hrs/Day = [fcstHrs]/5
Days1 = Day([WEDate])
Days2 = 5 - Day([WEDate])
Hrs1 = Hrs/Day * Days1
Hrs2 = Hrs/Day * Days2

Unfortunately I can't work out how to implement this logic so any ideas you guys have would be greatly appreciated. Also if you know of a better method to accomplish this feel free to let me know.



Travis

http://www.apexsystemsinc.com

 

[PWise]

what fields do you have in the forcast table

 

[newguy86]

PWise,
Here you go:

ID - Unique ID for each record
Pnum - Project that the forecast applys to
assignID - Assignment that the forecast applys to
WEDate - Weekending Date that the forecast applys to
fcstHrs - # of forecasted hours


Travis

http://www.apexsystemsinc.com

 

[PWise]

This is what i would do

have a dates table
dates
1/1/12
1/2/12
1/3/12
.....
12/29/20
12/30/20
12/31/20


have a weeks table
wekkstart weekend
1/2/12 1/6/12
1/9/12 1/13/12
....
12/21/20 12/25/20
12/28/20 1/1/21



Select Pnum year(dates) ,month (dates) fcstHrs /count(*)
inner join week table
on WEDate between weekstart and weekend
inner join dates
on dates between between weekstart and weekend
Group by Pnum ,year(dates) ,month (dates),fcstHrs

 

[PWise]

Sorry sb
inner join week table
on WEDate = weekend
that is if wedate is always fridays