Problem getting YTD number count.

[Vegboy]

Hi, everyone...

trying to get the project done before new year comes =D
somehow i cannot pass the YTD case-count.

come here look for help.

okay,

here is what we want to do...

already created a database with hundreds of cases, they look like this,

Case# // Case Created Date //Current Status // Item Name
001 // 12/5/06 // Processing // Item A
002 // 12/1/06 // Pending // Item B
003 // 12/2/06 // Processing // Item C
004 // 11/5/06 // Done // Item D
005 // 08/5/06 // Done // Item E
006 // 06/5/06 // Pending // Item F

We have no problem counting number of case for every month.
example,
Monthly Summary: December
Number of Processing Case:2
Number of Pending Case:1
Number of Done Case: 0
...
Monthly Summary: November
Number of Processing Case:0
Number of Pending Case:0
Number of Done Case: 1
...
Monthly Summary: August
Number of Processing Case:0
Number of Pending Case:0
Number of Done Case: 1
...
Monthly Summary: June
Number of Processing Case:0
Number of Pending Case:1
Number of Done Case: 0
...

And this is what we are trying to get to.
using the sample db above,

YTD(year to date) Number of Processing Case: 2
YTD Number of Pending Case: 2
YTD Number of Done Case: 2

Thanks.

 

[pbaldy]

Whatever way you used to restrict records to the specific month, modify for the selected year. There are some date functions here that may help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210604