Code:
SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, IIf(Sum(IIf(DatePart("m",[paymentdate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=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])>=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])>=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])>=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])>=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])>=7 Or DatePart("m",[paymentdate])<=6,1,0))) AS JunFROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((attribute_PersonMembership.PaymentDate) Between DateAdd("yyyy",-4,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),Month(Date()-1),0) Or (attribute_PersonMembership.PaymentDate) Is Null) AND ((lookup_MemberTypes.Id)=3))
GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy")
HAVING (((Format([EndDate],"yyyy"))>=2014))
ORDER BY Format([EndDate],"yyyy");
1. the Collegiate members have longer seasons, generally the Collegiate memberships are 4 years long, 4 seasons long, whereas all other member groups are only 1 season, 1 year.