Limiting a query

I have a grant that one staff member's salary comes from. There can only be
one staff member that gets paid from that grant at a time. If this person
cares for a client from HUD or Both programs I need to sum of the
LengthofService by month and also do year to date summations. I have had to
revise the program so that more than one person may be paid by the grant
money during the year, for example Hardy worked the grant between 3/25/2002
to 4/31/2004 and Alexander started on the grant from 5/1/2004. The grant
runs from 7/1/2003 to 6/30/2004. Alexander may have worked with HUD and Both
programs during the year but I needs his numbers for only May and June of
04.

Now to the results. I have data through the end of May, but I need to go
back and get February data. So for the month I want only numbers for Hardy
because she is the only person working on grant funds in February 2004. If I
only need May's data only Alexander's data should be reported. When I total
for the year I need to get the sum of Hardy between July 03 through April 04
and Alexander's numbers for May and June.

I started with this, but I am not getting any where.
Between DLookUp("StartDate","qryHUDStaff") And DLookUp("End","qryHUDStaff")

Thanks for your help.

Fay

Hi,


If the table has fields GrantStart and GrantEnd and you wish to consider the
portion of grant that overlap the report period reportStart, reportEnd, you
can try:


SELECT iif(reportStartGrantStart, reportStart, GrantStart) As Starting,
iif(reportEndGrantEnd, reportEnd, GrantEnd) As Ending,
DateDiff("m", Starting, Ending) As OverlappedMonthsForThisRecord
FROM myTable
WHERE reportStart=GrantEnd AND reportEnd=GrantStart


if you want the sum (in number of month):

SELECT SUM(DateDiff("m",
iif(reportStartGrantStart, reportStart, GrantStart),
iif(reportEndGrantEnd, reportEnd, GrantEnd)
) )
FROM myTable
WHERE reportStart=GrantEnd AND reportEnd=GrantStart



Note that this use DateDiff("m", , ), so, it inherits its way of "counting"
(we should speak of number of month changes rather than difference in month,
to be closer to what DateDiff does).



Hoping it may help,
Vanderghast, Access MVP


"Fay Yocum" wrote in message
...
I have a grant that one staff member's salary comes from. There can only
be
one staff member that gets paid from that grant at a time. If this person
cares for a client from HUD or Both programs I need to sum of the
LengthofService by month and also do year to date summations. I have had
to
revise the program so that more than one person may be paid by the grant
money during the year, for example Hardy worked the grant between
3/25/2002
to 4/31/2004 and Alexander started on the grant from 5/1/2004. The grant
runs from 7/1/2003 to 6/30/2004. Alexander may have worked with HUD and
Both
programs during the year but I needs his numbers for only May and June of
04.

Now to the results. I have data through the end of May, but I need to go
back and get February data. So for the month I want only numbers for Hardy
because she is the only person working on grant funds in February 2004. If
I
only need May's data only Alexander's data should be reported. When I
total
for the year I need to get the sum of Hardy between July 03 through April
04
and Alexander's numbers for May and June.

I started with this, but I am not getting any where.
Between DLookUp("StartDate","qryHUDStaff") And
DLookUp("End","qryHUDStaff")

Thanks for your help.

Fay