Windows 7 32bit 
Access 2003
I'm not still comfortable on the usage with DateAdd, DateSerial, etc ...
And I'm not sure how to check to verify this easily ...
Will this statement give me cumulative total including last June's?
WHERE: Between DateAdd("yyyy",-2,DateSerial(Year(Date()),6,1)) And DateAdd("yyyy",-1,DateSerial(Year(Date()-1),6,30))Code:Jun: IIf(Sum(IIf(DatePart("m",[PaymentDate])=6 And DatePart("yyyy",[paymentdate])=DatePart("yyyy",Date()-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0)))
And the first column is July, which should include 2 years ago June and July's:
Basically it is for a fiscal year from a year ago and the fiscal month is from prior July to June.Code:Jul: IIf(Sum(IIf((DatePart("m",[PaymentDate])=6 Or DatePart("m",[PaymentDate])=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-2),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And DatePart("m",[PaymentDate])<=7,1,0)))
I'm trying to see what each month totals were last fiscal year. Which would be from June 2012 and July 2012 into the "Jul" column where "Aug" would include Jun - Aug 2010 and so on.
And the "Jun" column should include the cumulative total from June 2012 to June 2013.
For some reason "Jun" is coming up blank and I know that is not correct.
MemberGroup MemberType Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Regular Member First Family 25537 27213 28715 29580 30014 30389 30697 30920 31056 31130
Regular Member Subsequent Family 12321 13050 13728 14214 14519 14738 14958 15111 15203 15279
Regular Member Introductory 2024 2472 2983 3288 3481 3758 4036 4297 4436 4531
Regular Member Individual First Family 1049 1167 1267 1358 1399 1465 1512 1552 1572 1590
Regular Member Individual Subsequent Fam 135 150 163 172 175 178 180 185 188 192
Basic Skills Member New Basic Skills Member 7477 13168 18998 25579 29581 40347 51339 57786 62535 66947
Basic Skills Member New Basic Skills Instructor 307 468 596 693 748 822 901 943 964 985
Basic Skills Member Renewing Basic Skills Member 8813 14325 19370 23349 25382 28512 31763 32878 33940 35338
Basic Skills Member Renewing Basic Skills Instructor 1193 1620 2063 2322 2445 2557 2683 2717 2793 2891
NOTE: this is like another post handling current fiscal year. NOW I need to get the prior fiscal year. They want a comparison report.
With that said, the ultimately, the final result is to show the reporting months comparison of last fiscal year and current fiscal year.
So somehow to dynamically always show the current month with last year's and current year's
Now I don't know if doing the prior fiscal year query of the current fiscal year query and using both in another query works. Because at the moment I'm not sure how to "dynamically" show the reporting month from this:
I had to select the month in this: which will not work come end of next month without going into it and changing it
MemberGroup MemberType 20_RetentionComparisonCurYr.Apr 20_RetenionComparisonPriorYr.Apr Basic Skills Member New Basic Skills Instructor 1112 964 Basic Skills Member New Basic Skills Member 67639 62535 Basic Skills Member Renewing Basic Skills Instructor 2687 2793 Basic Skills Member Renewing Basic Skills Member 32410 33940 Regular Member First Family 31518 31056 Regular Member Individual First Family 1535 1572 Regular Member Individual Subsequent Fam 175 188 Regular Member Introductory 4567 4436 Regular Member Subsequent Family 15215 15203
And was thinking to possibly do this BUT not sure how to get the cumulative counts:Code:SELECT [20_RetentionComparisonCurYr].MemberGroup, [20_RetentionComparisonCurYr].MemberType, [20_RetentionComparisonCurYr].Apr, [20_RetenionComparisonPriorYr].Apr FROM 20_RetentionComparisonCurYr INNER JOIN 20_RetenionComparisonPriorYr ON ([20_RetentionComparisonCurYr].MemberGroup = [20_RetenionComparisonPriorYr].MemberGroup) AND ([20_RetentionComparisonCurYr].MemberType = [20_RetenionComparisonPriorYr].MemberType) GROUP BY [20_RetentionComparisonCurYr].MemberGroup, [20_RetentionComparisonCurYr].MemberType, [20_RetentionComparisonCurYr].Apr, [20_RetenionComparisonPriorYr].Apr;
Code:SELECT dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]) AS PaymentYear, Month([PaymentDate]) AS PaymentMonth FROM dbo_v030mbrshp01PdMembers GROUP BY dbo_v030mbrshp01PdMembers.MemberTypeID, dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, Year([PaymentDate]), Month([PaymentDate]) HAVING (((Year([PaymentDate])) Between Year(Date())-1 And Year(Date())) AND ((Month([PaymentDate]))=Month(Date()))) ORDER BY dbo_v030mbrshp01PdMembers.MemberTypeID, Year([PaymentDate]);
MemberTypeID MemberGroup MemberType PaymentYear PaymentMonth CountOfMembershipNumber 1 Regular Member First Family 2013 4 136 1 Regular Member First Family 2014 4 144 2 Regular Member Subsequent Family 2013 4 92 2 Regular Member Subsequent Family 2014 4 118 4 Regular Member Introductory 2013 4 139 4 Regular Member Introductory 2014 4 154 6 Regular Member Individual First Family 2013 4 20 6 Regular Member Individual First Family 2014 4 18 7 Regular Member Individual Subsequent Fam 2013 4 3 7 Regular Member Individual Subsequent Fam 2014 4 4 9 Basic Skills Member New Basic Skills Member 2013 4 4749 9 Basic Skills Member New Basic Skills Member 2014 4 8215 10 Basic Skills Member New Basic Skills Instructor 2013 4 21 10 Basic Skills Member New Basic Skills Instructor 2014 4 57 41 Basic Skills Member Renewing Basic Skills Member 2013 4 1062 41 Basic Skills Member Renewing Basic Skills Member 2014 4 1335 43 Basic Skills Member Renewing Basic Skills Instructor 2013 4 76 43 Basic Skills Member Renewing Basic Skills Instructor 2014 4 18
To look like this WITH cumulative totals though:
MemberTypeID MemberGroup MemberType Apr-2013 Apr-2014 1 Regular Member First Family 136 144 2 Regular Member Subsequent Family 92 118 4 Regular Member Introductory 139 154 6 Regular Member Individual First Family 20 18 7 Regular Member Individual Subsequent Fam 3 4 9 Basic Skills Member New Basic Skills Member 4749 8215 10 Basic Skills Member New Basic Skills Instructor 21 57 41 Basic Skills Member Renewing Basic Skills Member 1062 1335 43 Basic Skills Member Renewing Basic Skills Instructor 76 18
Reply With Quote
