Convert date range into separate months - ideas?

[wendy]

I have a table with projects that have a start date, an end date and
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]

i haven't tried it, but you might be able to get the result you want with a
crosstab query, using a formatted StartDate, as

MyDate: Format([StartDate],"m\/yy")

hth
<we***@brawnandfervor.comwrote in message
news:11*********************@y42g2000hsy.googlegro ups.com...

I have a table with projects that have a start date, an end date and
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!

[wendy]

Wow. Thank you, this works beautifully!!