I can't figure out why the total numbers are not reconciling?
SELECT [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=6 Or DatePart("m",[PaymentDate])<=7) 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])>=5 And 7 Or DatePart("m",[PaymentDate])<=1,1,0))) AS Jan, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 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])>=6 And 7 Or 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])>=5 And 7 Or 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])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS May, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or 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
FROM 02_Collegiate_Details
GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;
MemberType Season Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Collegiate
2016 520 617 716 795 822 836 856 874 887 889 889 889 Collegiate 2017 537 622 694 815 857 871 903 923 933 943 943 943 Collegiate 2018 638 732 839 900 948 984 1032 1054 1062 1067 1067 1067 Collegiate 2019 696 779 871 970 1016 1032 1032 1032 1032 1032 1032 1032
The above code is producing 520 however if you total the records from the 02_Collegiate_Details, I get 507 for the date range Jun-Jul
Thoughts and suggestions?