Query that

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

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

"Matthew Pfluger" wrote:

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 wrote:
I apologize for the horrible subject. I forgot to complete my thought.

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.
[quoted text clipped - 21 lines]

Matthew Pfluger

Use this function in your query VARIANCE: DateDiff("m",#beginingdate#,
#endingdate#)
This function will calculate the difference between the two dates, and using
the total hours, divide by the variance to get hours per month.

Example:
VARIANCE: DateDiff("m",#6/24/2008#,#11/26/2008#)
HR PER MONTH: 100/[VARIANCE]

--
Message posted via http://www.accessmonster.com

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])));

--
KARL DEWEY
Build a little - Test a little


"Matthew Pfluger" wrote:

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