Convert date range into separate months - ideas?
we...@brawnandfervor.com
total hours, for example:
Project StartDate EndDate Hours
Plumbing 3/16/07 5/3/07 240
Drywall 2/3/07 3/4/07 94
Electrical 3/20/07 4/4/07 86
What I want is to have a report that shows me how many hours each
month are spent on a particular project, assuming that regardless of
the particular day of the month the project was started or ended, the
project's hours are evenly divided among months (for example, above
Plumbing should be divided evenly among March, April, and May, even
though the project only extends 3 days into May). The Report I
eventually end up with I want to look like this:
Project 2/07 3/07 4/07 5/07
Plumbing 0 80 80 80
Drywall 47 47 0 0
Electrical 0 43 43 43
How do I take a table with start date and end date columns and convert
it into months? I'm dealing with several thousand records, so I really
would like to avoid doing this manually! Thanks for any ideas!
tina
crosstab query, using a formatted StartDate, as
MyDate: Format([StartDate],"m\/yy")
hth
<we...@brawnandfervor.com> wrote in message
news:1189478177....@y42g2000hsy.googlegroups.com...
CDMAP...@fortunejames.com
I'll start with an auxiliary function.
'---Begin Module Code---
Public Function DateIntersection(dt1 As Date, dt2 As Date, dt3 As
Date, dt4 As Date) As Integer
'Return the number of days overlapping two date ranges
'Assumes d1 <= d2 and d3 <= d4 Ranges can go in either order
DateIntersection = 0
If dt2 <= dt3 Then
If dt2 = dt3 Then DateIntersection = 1
Exit Function
End If
If dt4 <= dt1 Then
If dt4 = dt1 Then DateIntersection = 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt3, dt2) + 1
Exit Function
End If
If dt1 <= dt3 And dt3 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt3, dt4) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt2 And dt2 <= dt4 Then
DateIntersection = DateDiff("d", dt1, dt2) + 1
Exit Function
End If
If dt3 <= dt1 And dt1 <= dt4 And dt4 <= dt2 Then
DateIntersection = DateDiff("d", dt1, dt4) + 1
End If
End Function
'---End Module Code---
tblProjects
PID AutoNumber
ProjectName Text
StartDate Date/Time
EndDate Date/Time
Hours Double
PID ProjectName StartDate EndDate Hours
1 Plumbing 3/16/2007 5/3/2007 240
2 Drywall 2/3/2007 3/4/2007 94
3 Electrical 3/20/2007 4/4/2007 86
tblHeadingDates
HDID AutoNumber
HeadingDate Date/Time Format: m/d/yy
HDID HeadingDate
1 2/1/07
2 3/1/07
3 4/1/07
4 5/1/07
qryForCrosstab:
SELECT DateDiff("m",DateSerial(Year(StartDate),Month(StartDate),
1),DateSerial(Year(EndDate),Month(EndDate)+1,1)) AS N, CDbl(Hours)/N
AS HoursForHeadingDate, tblProjects.ProjectName,
tblHeadingDates.HeadingDate, tblProjects.StartDate,
tblProjects.EndDate, tblProjects.Hours FROM tblHeadingDates,
tblProjects WHERE
DateIntersection([HeadingDate],DateSerial(Year(HeadingDate),
Month(HeadingDate) + 1, 0),[StartDate],[EndDate])>0;
!qryForCrosstab:
N HoursForHeadingDate ProjectName HeadingDate StartDate EndDate Hours
2 47 Drywall 2/1/07 2/3/2007 3/4/2007 94
3 80 Plumbing 3/1/07 3/16/2007 5/3/2007 240
2 47 Drywall 3/1/07 2/3/2007 3/4/2007 94
2 43 Electrical 3/1/07 3/20/2007 4/4/2007 86
3 80 Plumbing 4/1/07 3/16/2007 5/3/2007 240
2 43 Electrical 4/1/07 3/20/2007 4/4/2007 86
3 80 Plumbing 5/1/07 3/16/2007 5/3/2007 240
qrySummaryCrosstab:
TRANSFORM Sum([HoursForHeadingDate]) AS [The Value] SELECT
[ProjectName], [HoursForHeadingDate] AS Hours,
Sum(HoursForHeadingDate) AS [Total Of Hours] FROM qryForCrosstab GROUP
BY [ProjectName], [HoursForHeadingDate] PIVOT Format([HeadingDate],"m/
yy");
!qrySummaryCrosstab:
ProjectName Hours Total Of Hours 2/07 3/07 4/07 5/07
Drywall 47 94 47 47 Null Null
Electrical 43 86 Null 43 43 Null
Plumbing 80 240 Null 80 80 80
Note: A variant of the ProrateMonth function found here:
http://groups.google.com/group/microsoft.public.access/msg/647849c4b4f6e56f
would give a much more accurate division of hours worked in a given
month on a project provided the distribution of hours is uniform. For
the solution shown above, the DateIntersection function can be
replaced by an appropriate SQL expression for greater range of
applicability.
James A. Fortune
CDMAP...@FortuneJames.com