I even tried this: http://support.microsoft.com/kb/290136
No good. Getting #Error#
and it freezes
Any idea why?
Code:
SELECT dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]) AS PaidYr, Month([PaymentDate]) AS FM, IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate])) AS FY, DCount("dbo_v030mbrshp01PdMembers","PaymentDate","DatePart('m', [PaymentDate])<=" & [FM] & " And DatePart('yyyy',[PaymentDate])<=" & [FY] & "") AS RunTot
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))))
GROUP BY dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]), Month([PaymentDate]), IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate]))
HAVING (((IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate])))>=Year(Date())-1))
ORDER BY Year([PaymentDate]), Month([PaymentDate]);
Then tried this: GOOD ONLY if doing 12 months. Still need help getting 15 months column headers
Code:
TRANSFORM Count(dbo_v030mbrshp01PdMembers.MembershipNumber) AS CountOfMembershipNumber
SELECT dbo_v030mbrshp01PdMembers.MemberType
FROM dbo_v030mbrshp01PdMembers
WHERE (((IIf(Month([PaymentDate])<=5,Year([PaymentDate])-1,Year([PaymentDate])))>=Year(Date())-1) AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4) AND ((dbo_v030mbrshp01PdMembers.PaymentDate) Between IIf(Month(Date())<=6,DateSerial(Year(Date())-1,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=6,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30))))
GROUP BY dbo_v030mbrshp01PdMembers.MemberType
PIVOT Format([PaymentDate],"mmm") In ("Jul","Aug","Sep","Oct","Nov","Dec","Jan","Feb","Mar","Apr","May","Jun");
Then do this to get the cumulative sum for each month. "Rolling cumulative months" with another query by adding the months together in each column calling out the previous column name for the next, etc ...
Same issue with the limit of column headings to only 12.
Any ideas at all?
I need this BUT without the year since the year changes and I want the months to be static.
MemberType |
Mar-13 |
Apr-13 |
May-13 |
Jun-13 |
Jul-13 |
Aug-13 |
Sep-13 |
Oct-13 |
Nov-13 |
Dec-13 |
Jan-14 |
Feb-14 |
Mar-14 |
Apr-14 |
May-14 |
First Family |
223 |
136 |
74 |
14858 |
8737 |
2313 |
1646 |
1771 |
847 |
329 |
381 |
333 |
207 |
143 |
39 |
Introductory |
261 |
139 |
95 |
921 |
819 |
518 |
433 |
507 |
312 |
163 |
274 |
237 |
232 |
156 |
54 |
Subsequent Family |
153 |
92 |
76 |
6667 |
4511 |
1217 |
723 |
873 |
447 |
181 |
194 |
191 |
157 |
119 |
23 |