If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Limiting a query
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 |
Thread Tools | |
Display Modes | |
|
|