Running Total on Amount for each Month?

[aellistechsupport]

Hello,


I currently have this

SELECT "Friends of FS" AS MemberGroup, dbo_v030mbrshp04FOFSAmounts.MemberType, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun, 

Count(dbo_v030mbrshp04FOFSAmounts.MembershipNumber) AS CountOfMembershipNumber, Sum(dbo_v030mbrshp04FOFSAmounts.Amount) AS SumOfAmount


FROM dbo_v030mbrshp04FOFSAmounts


WHERE (((dbo_v030mbrshp04FOFSAmounts.PaymentDate) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1) Or (dbo_v030mbrshp04FOFSAmounts.PaymentDate) Is Null))
GROUP BY "Friends of FS", dbo_v030mbrshp04FOFSAmounts.MemberType, dbo_v030mbrshp04FOFSAmounts.MemberTypeID
ORDER BY dbo_v030mbrshp04FOFSAmounts.MemberTypeID;

The above query is the total running count in each month based on the PaymentDate.

Result:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	CountOfMembershipNumber	SumOfAmount
Friends of FS	Friend	1	2	2	4	5	15	24	27					27	1146
Friends of FS	Friend - Red Level						14	21	22					22	2250
Friends of FS	Friend - White Level						2	7	10					10	2262

How do I still MAINTAIN the PaymentDate ranges to get the specific months BUT do a RUNNING TOTAL on the "Amount" field for each month?

Where the BOLDED part of the query statement is the grand total of the number of MembershipNumbers and the grand total of the Amounts

If I have to break it out into two queries, one for the total counts each month and another for the total amounts for each month.

I want to see something like this (this is from a basic cross-tab query) BUT with a RUNNING TOTAL SUM and Jul includes last Jun's total amount.

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Friends of FS	Friend	$12.00	$12.00		$8.00	$4.00	$500.00	$450.00	$160.00
Friends of FS	Friend - Red Level						$1,400.00	$750.00	$100.00
Friends of FS	Friend - White Level						$500.00	$1,250.00	$512.00

[rpeare]

instead of counting membership numbers sum the amount field

[aellistechsupport]

right, but I'm not sure HOW to while maintaining the monthly columns with the date range criteria?

[rpeare]

in all of your formulas where you have something like:

IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0))) AS Jul,

You'll have to change it to something like:

IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And  DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6  And DatePart("m",[paymentdate])<=7,[INSERT PAYMENT FIELD NAME HERE],0))) AS Jul,

[aellistechsupport]

THANK YOU!! It seems to be working.
I'm applying it to the other months now and will post back ONLY IF I'm having issues where something is not working.

Thanks again!