Query that

[Matthew Pfluger]

I am designing a database that stores tasks, hours, start dates, and end
dates. Management wants to see an estimated hours by month for each task.

Example: Task A is estimated to require a total of 100 hours, and it will
range from 7/1/08 to 10/31/08. Management wants to see this report:

Task Month Hrs
A 2008-07 25
A 2008-08 25
A 2008-09 25
A 2008-10 25

The trick here is that the query needs to figure out the date range of each
task, determine which months are within the range, and then determine how
many hours per month. The months are not stored anywhere except in the Start
and End dates.

Right now, I'm solving this task by exporting all tasks, hours, and dates to
Excel, and running a custom macro. It works, but the report needs to include
other information about each task that would lend the task to a query.

Is this possible in a query? If not, is there a better workflow than using
Automation to Excel? Thanks.

Matthew Pfluger

 

[Matthew Pfluger]

I apologize for the horrible subject. I forgot to complete my thought.

 

[KARL DEWEY]

This does not take into account part months. Create a table named
CountNumber with field named CountNUM containing number from 0 (zero) through
your maximum spread.

SELECT Matthew.tasks, Format(DateAdd("m",[CountNUM],[start date]),"yyyy-mm")
AS [Month], [hours]/(DateDiff("m",[start date],[end date])+1) AS Hrs
FROM CountNumber, Matthew
WHERE (((CountNumber.CountNUM)<=DateDiff("m",[start date],[end date])));