Calculate MTD (Month To Date) in Query Table (1 Viewer)

[mnazera]

I have query like this

H2ExPSA-MTD H2ExPSA-MTD ID Date H2ExPSATotal Total 2 1/1/2011 58584 58584 3 1/2/2011 49116 107700 5 1/3/2011 49184 156884 6 1/4/2011 48828 205712 7 1/5/2011 48796 254508 8 1/6/2011 48748 303256 9 1/7/2011 48996 352252 10 1/8/2011 44900 397152 11 1/9/2011 32764 429916 12 1/10/2011 55644 485560 13 1/11/2011 18360 503920 14 1/12/2011 30000 533920 15 1/13/2011 0 533920 16 1/14/2011 0 533920 17 1/15/2011 0 533920 18 1/16/2011 0 533920 19 1/17/2011 0 533920 20 1/18/2011 0 533920 21 1/19/2011 0 533920 22 1/20/2011 0 533920 23 1/21/2011 0 533920 24 1/22/2011 0 533920 25 1/23/2011 0 533920 26 1/24/2011 0 533920 27 1/25/2011 0 533920 28 1/26/2011 0 533920 29 1/27/2011 0 533920 30 1/28/2011 0 533920 31 1/29/2011 0 533920 32 1/30/2011 0 533920 33 1/31/2011 0 533920 34 2/1/2011 0 533920 35 2/2/2011 0 533920
Use SQL :

SELECT ID, DateShift1, H2ExPSATotal, (SELECT Sum(H2ExPSATotal) As Total FROM H2ExPSASummary WHERE H2ExPSASummary.DateShift1 <=T1.DateShift1) AS Total
FROM H2ExPSASummary AS T1;

Pls help me, how calculate MTD, meaning every Date 1 of Month, value Total back to initial record value, not continue calculate value if the month was change

Thank

 

[John Big Booty]

You could use the following as your criteria;

Iif( [URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("d",date())<>1, Between [URL="http://www.techonthenet.com/access/functions/date/dateadd.php"]DateAdd[/URL]("d", (DatePart("d",Date())-1)*-1, Date()) And Date(), Date())

To return dates between the current date and the start of the month.

 

[mnazera]

please, where I put this codes ?

Thank

 

[John Big Booty]

Put it in the Criteria row under your date field.