This is in regards to our discussion about reporting on employee time projections.

I will try to be clearer.

A) Datasheet one includes information about employee time commitments.

It might say:
John, 20% commitment, Project A, from 1/4/10, to 1/22/10
John, 40% commitment, Project B, from 1/18/10, to 4/11/10
Mike, 14% commitment, Project A, from 2/12/10, to 5/11/10

B) I have created a query that calculates each week.

C) I WOULD LIKE to create a report that tells me that, say:

Week of 1/18/10, John is 60% committed
Week of 1/18/10, Mike is 0% committed.
....
Week of 2/22/10, John is 40% committed.
Week of 2/22/10, Mike is 14% committed.
...(and so on)

D) So far, I have created a timesheet (as described in part A), a week-calculating query (as you explained to me), and a report that includes aggregate queries.

My 'aggregate query report' groups by BOTH 'personnel' and 'calculated-wee.'  It SUMS 'time committed.'  The problem I have now is that EVERY week reported sums all time commitments.

So, the report incorrectly (using the example above) says:
Week of 1/18/10, John is 60% committed
Week of 1/18/10, Mike is 14% committed.
....
Week of 2/22/10, John is 60% committed.
Week of 2/22/10, Mike is 14% committed.
...(and so on)

Mike is ALWAYS shown as 14% committed, John ALWAYS as 60% committed.  Needless to say, I need a different sum for each week.

Thanks!!!

---

Answer from Richard Rost:

You'll have to get a little more creative then. You'll probably have to use a doman function like DSUM to add up the commitment field for each employee by dates. For example:

X: DSUM("Commitment","EmployeeTable","EmployeeID=" & EID & " AND StartDate > #" & D1 & "# AND EndDate <= #" & D2 & "#")

That should add up all of the commitment rates for a specific employee for those criteria. I would make a form where you could specify your StartDate and EndDate values (D1, D2) and then you could substitute those into your formula:

Forms!MyForm!StartDate
Forms!MyForm!EndDate

Now just run your employee query with the week start dates, and then add that DSUM function above as a calculated field.

It's a bit of work, but it SHOULD give you the results you want.

I don't have a FREE tutorial that covers DSUM, but I do have one for  DLOOKUP, which is a close relative.

I cover DSUM in my full Access 310 tutorial: http://599cd.com/site/courselist/access/access310?key=BlFAlEx

Here's a tutorial that will help you understand DLOOKUP:

You can use the DLOOKUP function to look up a specific value from a table or query.

Please see the following FREE video tutorial on my web site for step-by-step instructions on how to use DLOOKUP:

http://599cd.com/tips/access/dlookup-function?key=BlFAlEx