Cumulative Sum of Counts for Static Months

[aellistechsupport]

Yea, sorry, trying to figure out another way to explain it.

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.
2. the Collegiate query needs to show current season, ending this year plus the future years since members that paid since last Jul to now would have future end dates. Generally for 4 years. Did I explain this right?

[aellistechsupport]

I am not addressing your second question until this first is worked out, my examples have been based on your original formulas if you want to change what is counted in the columns that's up to you but update your SQL code here so I am not working on outdated information.

I appreciate your patience and working with me very much.

Yes, originally posted the counts on ID for each month of payment.

(Count([attribute_PersonMembership].[PersonId])

Tried using that MS article to do a DSum which resulted in the #Error that led me to this forum to see about how to get around that.

Thank you!

[rpeare]

I am having a really hard time getting my point across today for some reason.

Just for the sake of argument let's say you have this recordset in your table

attribute_PersonMembership

PersonID EndDate PaymentDate
PER001 6/30/2017 1/1/2014
PER002 6/30/2017 1/1/2013

In your query this will dump BOTH memberships into the JANUARY column though they are in different FISCAL YEARS according to their payment date. I don't believe that is what you want and you did not comment on or give a return example based on my basic data set.

Assume for the sake of argument this is your data set (I am going to spell it out again)
Assume also these are collegiate level people.

PersonID	EndDate	PaymentDate
PER001	6/30/2017	7/1/2014
PER002	6/30/2017	8/1/2014
PER003	6/30/2017	9/1/2014
PER004	6/30/2017	10/1/2014
PER005	6/30/2017	11/1/2014
PER006	6/30/2017	12/1/2014
PER007	6/30/2017	1/1/2015
PER008	6/30/2017	2/1/2015
PER009	6/30/2017	3/1/2015
PER010	6/30/2017	4/1/2015
PER011	6/30/2017	5/1/2015
PER012	6/30/2017	6/1/2015
PER013	6/30/2017	7/1/2015
PER014	6/30/2017	8/1/2015
PER015	6/30/2017	9/1/2015
PER016	6/30/2017	10/1/2015
PER017	6/30/2017	11/1/2015
PER018	6/30/2017	12/1/2015
PER019	6/30/2017	1/1/2016
PER020	6/30/2017	2/1/2016
PER021	6/30/2017	3/1/2016
PER022	6/30/2017	4/1/2016
PER023	6/30/2017	5/1/2016
PER024	6/30/2017	6/1/2016

This is your dataset for collegiate membershipIDs

What do you want your end result to look like. The code I gave you will look like this:

MemberType	SeasonEnding	PaymentFiscalYear	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2017	2015	1	2	3	4	5	6	7	8	9	10	11	12
Collegiate	2017	2016	1	2	3	4	5	6	7	8	9	10	11	12

[aellistechsupport]

Ah, got it.
RE:

attribute_PersonMembership

PersonID EndDate PaymentDate
PER001 6/30/2017 1/1/2014
PER002 6/30/2017 1/1/2013

In your query this will dump BOTH memberships into the JANUARY column though they are in different FISCAL YEARS according to their payment date. I don't believe that is what you want and you did not comment on or give a return example based on my basic data set.

That makes sense. Since the month columns aren't distinguishing the years of the payments yet having to show the varying "seasons". Sorry about. I think it did but was trying to see IF there was a way NOT to show the Payment year but wasn't thinking it thoroughly for whatever reason. Well, the reason I was stuck on is simply not wanting to show the Payment year block.

Not sure how they got around the before but I understand it to explain it to them better as to why the Payment year has to show to make sense.

The results for the queries should look like this EXCEPT it needs to be a running sum of the counts each month:
This is counts for each month only:

MemberType	SeasonEnding	PaymentYear	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2014	2010	283	91	78	100	48	16
Collegiate	2014	2011	1	2	1		1		13	12	13	6	4	1
Collegiate	2014	2012		2		2
Collegiate	2014	2013		1		1			1					9
Collegiate	2015	2011	275	100	66	81	46	28						266
Collegiate	2015	2012		2					28	18	14	5	3	1
Collegiate	2015	2013	1	1
Collegiate	2016	2012	236	93	99	76	26	11						274
Collegiate	2016	2013	4	1	1				18	17	12	2	2	6
Collegiate	2017	2012					1
Collegiate	2017	2013	242	82	69	118	38	16						290
Collegiate	2017	2014							32	20	10

[rpeare]

You didn't adapt the formulas correctly:

SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, DatePart("yyyy",[paymentdate]) AS PaymentYear, 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])=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])=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])=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])=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])=12,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(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 Jun
FROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
WHERE (((lookup_MemberTypes.Id)=3))
GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate])
HAVING (((Format([EndDate],"yyyy"))>=2014))
ORDER BY Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate]);

[aellistechsupport]

Gotcha. the Payment Fiscal Year.

Thank you!


How about the running counts on membershipID to get results for these parameters?

The In Active, Active 0 Mbrshp, Active, Active Not Paid?

Post #23
https://www.accessforums.net/queries...tml#post222086

and #27
https://www.accessforums.net/queries...tml#post222105

[rpeare]

Post 23... again, the same question, how do you intend to count members by month? All of your measurements that you've put out are based on the payment date, if you have no payment date you can't group them into the month columns. The only date you have left is the ENDDATE which I don't think is going to give you what you want unless you're after a membership expiration query. Unless you have some other date or some other criteria for grouping a member who hasn't paid into months on a date that wasn't in your example I don't see how you can do this.

[aellistechsupport]

Sorry, thought I answered that.
I was originally counting the ID.

So the running count should be on the ID, as there will always be one but not always a PaymentDate.

A member (ID) record is created for a membership period (StartDate and EndDate) however they may or may not have paid (PaymentDate is Null).

The uploaded db has the cross-tab query with the value as count on ID for each PaymentDate as month.

I didn't realize the running totals query provided was counting the PaymentDate. I was just so excited to have the running totals and went onto the other Member group queries until I came across these scenarios.

[rpeare]

Sorry that explanation does not do me any good. You have a start date, an end date and a payment date. Which of those dates are you using to determine which month an unpaid member falls in?

[aellistechsupport]

Oh, sorry ... It's PaymentDate

The end date is only important in knowing when the membership ends and since membership record is entered even when not paid or on some member groups do not have to pay, management wants to see how many did not pay who's active and not active, etc ...

So in the current fiscal year, how many members are Active but have not paid, how many are In Active and so on.

1. Active members not paid. <--- Status = 3 and EndDate >= today where PaymentDate is Null for MemberTypeID = 20 or 21
2. Active members that paid. <--- since the other queries do not look at status, only paid memberships of each month in the fiscal year, this is to see all Active members that paid weeding out the In Active ones.
3. Active but no memberships. <--- Status = 3 where MemberTypeID = 16 (BasicSkillsProgram). basic skills programs don't have to pay club dues - if their status is active and they have 0 members they're 'active w/ 0', if they're inactive, they're just inactive.

So I was thinking this

IIf([Status]<>3,"Inactive",(IIf([Status]=3 And [PaymentDate] Is Null,"Active 0 Mbrshp","Active")))

Tested in the Regular Members query and I get this: for "Active 0 Mbrshp", it's currently trying to count PaymentDate and since there aren't any it can't count blanks.
Why all the counts should be on ID.

MemberGroup	MemberType	Expr1	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	Inactive	8	9	10	12	12	12	13	14
Regular Member	First Family	Active 0 Mbrshp
Regular Member	First Family	Active	8793	11113	12761	14534	15383	15713	16094	16426	16635
Regular Member	Introductory	Inactive						2	2	4	5
Regular Member	Introductory	Active 0 Mbrshp
Regular Member	Introductory	Active	823	1344	1777	2284	2596	2760	3034	3270	3501
Regular Member	Subsequent Family	Inactive	3	3	3	5	5	6	9	11
Regular Member	Subsequent Family	Active 0 Mbrshp
Regular Member	Subsequent Family	Active	4537	5765	6496	7369	7823	8007	8198	8387	8544

[rpeare]

I think I'm giving up

I asked

Which of those dates are you using to determine which month an unpaid member falls in?

You said paymentdate

I've explained at least twice that you can't use a null field (PAYMENTDATE is null) to determine which month an unpaid person belongs to. I don't know how much clearer I can be on that.

If you want further help please give a very LIMITED and explicit set of data that follows the format of the tables in your example database and what you expect the output to be.

[aellistechsupport]

Yes, I see what you're saying.

Since the BasicSkillsProgram (MemberTypeID = 16) will never have a payment date and can't report on that.

This is what I was able to find out, and will be further confirming tomorrow, is that they run the count on the Active members and In Active members.

There are member records with no dates at all in the membershipdates or paymentdate.
Those are the counts they want each month for couple of these queries.

Not based on any date filter using the existing date fields but rather live date. So just run a count on the ID for the Basic Skills Program members and if today, or rather the first day of the next month, so in this case run on May 1st to get the total number of Active members and In Active members for April. They save that number in Excel then manually add it to May on June 1st, etc ...

So I think a temp table of the counts for the prior months need to be stored to then use to add the counts for the next run of counts.

I uploaded an updated db with everything. I had to delete the record set due to the size limit of the upload.

[rpeare]

There's a table missing from your example (lookup_membertypes) if I'm supposed to look at an existing query.

I do not understand your explanation at all. You mention 'basic skills' which is query 05_BasicSkills but that query won't work without the table listed above.
In that query the member ID's are 9, 10 , 41 and 43, there are zero (0) records with a member type 16 in your example data.

A majority of the records in the table for membership ID's 9, 10 , 41, and 43 start at the beginning of the fiscal year and end on the last day of the fiscal year, you have a few exceptions where the membership starts and ends on the same day, or may go for 3 months.

For instance for memberships starting on 7/1/2013 you have 51,766 records

of those 51,766 records you have 51,018 that start on the first day of the fiscal year and terminate on the last day of the fiscal year. (98.55%)
you have a further 428 records that start on 7/1/2013 and end on 7/1/2013 (.82%)
That means the rest of your records combined, 320 of them, comprise about .62% of your members.

So assuming you were to show a distribution by month based on the ENDDATE for these people it would, ultimately, be meaningless because such a vast majority of your clientelle are clustered at the very first or very last day of the year.

If you are trying to find out how many people you have active per month that, also, would be meaningless because you would have the same 51,018 people active every month for the whole year and a fluctuation of say 30 per month is going to be absolutely meaningless as well in looking at your data.

[aellistechsupport]

Sorry about that. the lookup_membertypes table info needed is actually in the dbo.v030nbrshp00 but I was using the lookup table in a few queries to see if it'll show be all of the member labels.

Plus the limit of 2mb on the file upload.

If you are trying to find out how many people you have active per month that, also, would be meaningless because you would have the same 51,018 people active every month for the whole year and a fluctuation of say 30 per month is going to be absolutely meaningless as well in looking at your data.

Yes. I agree. This is something I'll be finding out today. They must be using some other date I am unaware of and perhaps in some other table because it's obviously not PaymentDate and can't use EndDate either as any of these date fields have Null records in them.

So I ran it for the 9, 10, 41 and 42 "ID" (which is the MemberTypeID)

The <> is the Null date. and the Counts are for each month. this is a basic cross-tab query

MemberGroup	MemberType	MemberStatus	<>	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13	Dec-13	Jan-14	Feb-14	Mar-14
Basic Skills Member	New Basic Skills Member	Active		3536	2388	5196	6360	5554	4170	10406	9698	11269
Basic Skills Member	New Basic Skills Member	Active 0 Mbrshp	47
Basic Skills Member	New Basic Skills Member	Inactive	1						89	378	52	21
Basic Skills Member	New Basic Skills Instructor	Active		105	83	162	125	117	51	161	127	80
Basic Skills Member	New Basic Skills Instructor	Active 0 Mbrshp	18
Basic Skills Member	New Basic Skills Instructor	Inactive					1			4
Basic Skills Member	Renewing Basic Skills Member	Active		4539	2368	4720	5272	3644	1419	2737	3356	1548
Basic Skills Member	Renewing Basic Skills Member	Active 0 Mbrshp	277
Basic Skills Member	Renewing Basic Skills Member	Inactive	3					2		1	2
Basic Skills Member	Renewing Basic Skills Instructor	Active		585	374	581	391	259	48	54	55	20
Basic Skills Member	Renewing Basic Skills Instructor	Active 0 Mbrshp	97
Basic Skills Member	Renewing Basic Skills Instructor	Inactive				2						1

Hopefully I will get the answers and have it figured out. Will let you know in a few hours.

Thanks again for helping.

[aellistechsupport]

I ran it with the query code that does the running totals but get this:

MemberGroup	MemberType	MemberStatus	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Basic Skills Member	New Basic Skills Instructor	Inactive				1	1	1	5
Basic Skills Member	New Basic Skills Instructor	Active 0 Mbrshp
Basic Skills Member	New Basic Skills Instructor	Active	105	188	350	475	592	643	804	931	1011
Basic Skills Member	New Basic Skills Member	Inactive						89	467	519	540
Basic Skills Member	New Basic Skills Member	Active 0 Mbrshp
Basic Skills Member	New Basic Skills Member	Active	3536	5924	11120	17480	23034	27204	37610	47308	58577
Basic Skills Member	Renewing Basic Skills Instructor	Inactive			2	2	2	2	2		3
Basic Skills Member	Renewing Basic Skills Instructor	Active 0 Mbrshp
Basic Skills Member	Renewing Basic Skills Instructor	Active	585	959	1540	1931	2190	2238	2292	2347	2367
Basic Skills Member	Renewing Basic Skills Member	Inactive					2	2	3	5
Basic Skills Member	Renewing Basic Skills Member	Active 0 Mbrshp
Basic Skills Member	Renewing Basic Skills Member	Active	4539	6907	11627	16899	20543	21962	24699	28055	29603

It seems it is not picking up records for the "Active 0 Mbrshp".
And now I wonder if what I was trying to say, counting on the member ID vs. counting the PaymentDate is part of it?