Code:
SELECT [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=2,1,0))) AS Feb, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=3,1,0))) AS Mar, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=4,1,0))) AS Apr, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM 02_Collegiate_Details
GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;
Here is the summary. So All payments from Jun 2011 from Season 2015 through Mar 2015 to Season 2018 where the Mar column totals 3834. But only shows 3817.
Code:
SELECT [02_Collegiate_Details].MemberType, Count([02_Collegiate_Details].membershipnumber) AS ReportingMonth, DateSerial(Year(Date()),Month(Date()),0) AS LastMonth
FROM 02_Collegiate_Details
WHERE ((([02_Collegiate_Details].EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND (([02_Collegiate_Details].PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,6,1),DateSerial(Year(Date()),6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),Month(Date()),0),DateSerial(Year(Date())+3,Month(Date()),0))))
Please advise.
Code:
SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
FROM dbo_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.MembershipNumber, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate
HAVING (((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,6,1),DateSerial(Year(Date())-3,6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),Month(Date()),0),DateSerial(Year(Date())+3,Month(Date()),0))) AND ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,1),DateSerial(Year(Date())+3,6,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))))
ORDER BY dbo_v030mbrshp02Collegiates.EndYear, dbo_v030mbrshp02Collegiates.PaymentDate, dbo_v030mbrshp02Collegiates.EndDate;
to exclude the prior years current month data set? Since we're reporting up to the months prior to the current month?