Cumulative Sum of Counts for Static Months

[rpeare]

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.

These were the stated definitions in post #40

1. None of the member types in this defintion match the membertype ID's in your query 05_BasicSkills
2. You haven't provided an updated query (SQL code) or the table to make 05_BasicSkills function from your most recent database upload
3. None of your previous posts regarding how you want the data grouped had anything to do with MemberTypeID's indicated in 05_BasicSkills, your post #40 indicates there's possibly some important differentiation between MemberTypeID's but it is not relating to the MemberTypeID's in 05_BasicSkills so, if ACTIVE, INACTIVE, and ACTIVE 0 MBRSHIP are your three divisions for 05_BasicSkills how are you determining which data goes in which bucket.

DO NOT REFERENCE A PREVIOUS POST, pretend this is a brand new request because you are confusing the hell out of me.

Your stated goal is to produce a crosstab query for basic skills memberships.
In plain english
1. How are you identifying basic skills memberships (I am assuming this is MemberTypeID's 9, 10, 41 and 42)
2. How are you Determining which person belongs in which bucket
3. Provide the SQL code or an updated database that has the missing data to make your current query 05_BasicSkills function.

[aellistechsupport]

DO NOT REFERENCE A PREVIOUS POST, pretend this is a brand new request because you are confusing the hell out of me.

Your stated goal is to produce a crosstab query for basic skills memberships.
In plain english
1. How are you identifying basic skills memberships (I am assuming this is MemberTypeID's 9, 10, 41 and 42)
2. How are you Determining which person belongs in which bucket
3. Provide the SQL code or an updated database that has the missing data to make your current query 05_BasicSkills function.

Was trying to collect more information on this and when I finally got the file location for me to review the network connections went down. So will have to see this tomorrow.

1. Correct. All of the separate queries will have membertypeid for identifying the member types. As they want to see the different member types in different ways. Some with member groups and some without. But that isn't a big factor here as that's easy to add.
2. I don't understand the question? None of this is based on person but rather the memberships by membertype. Now I was using the PersonID or MembershipID to get the counts of memberships for the membertypes. As that's the goal.
3. Since the lookup_MemberTypes table isn't necessary it can be removed and just using the MemberTypeID in the dbo.v030mbrshp00 table. I can't open the .accdb file on my home laptop. I'll add tomorrow.

And with more info on how they were getting the counts of Active, In Active, Active w/ 0 mbrshp for each month on the Basic Skills Program when they don't pay for their memberships.

Thank you

[aellistechsupport]

scratching my head ...
this is the response I got:

for basic skills programs we want to know the count
of programs on the day we run the report
we know that basic skills don't pay club dues but that's the data we want

So at this point, what I'm getting is that for all of the queries the counts of the various memberships are based on the day they run the report.
Which they'll either always run it on at the end of the business of the month or the beginning of the business day of the next month.

Of course I'm a bit at a loss as to how they are going to get the numbers of the past months when they have all ready past.

I'm trying to find this out. How they are going to get that or have gotten that in the past.

So I've asked it this way:

I am not understanding how you are going to get the count for a past month if that is what you're trying to use is the “system date” (date the report is ran).
So to get the number of records, if you're not using a date in the database but a system date of when you run the report, how do you get Feb.'s counts if you're running it today?

If you're using a date in the database you will know all the records in Feb. for instance. But which database date?
The PaymentDate could work and I can see how someone wants to know how many memberships were paid in a given month.

Or how many memberships were created in a month. RE: "CreationDate" of the membership?

I think that it would make sense, based on what I’ve gathered so far, is to use the membership creation date.

Since, if I understand the records in the memberships table, is that a membership record is created for the member or program or club.
It be in the PersonMembership’s table for the person’s memberships info or the OrgMembership’s table to get the club / program memberships info.
But that these tables retain memberships when the membership is created, correct?

If so, then it makes the most sense to use the creation date of the membership info in these said tables.

This means you would get the number of memberships when they were created each month during your fiscal year.
Would that be correct as to what you’re looking for?

As initially, since I’ve been working with a lot of reports based on PaymentDate, I was getting the number of memberships when they paid, the month they paid them. But since the programs do not pay dues, the PaymentDate does not make sense for the programs.

So I think the best way to go about this is in fact to use the creationdate.

I'll be reworking the queries based on this unless they tell me otherwise.

So my question to you is I need to have the number of memberships show cumulative numbers each month in the fiscal year.
It needs to count the membershipID. Not the date.

[rpeare]

I don't see a question in there or a starting point for your SQL query. If your question is simply how do I count a field other than the date field I'm currently counting, you just substitute in the name of the field you want to count instead.

[aellistechsupport]

How to get monthly columns for the cumulative counts on the MembershipID?

This was provided to get each of the months of the fiscal year: Which works great if counting the number of date records.

Jul: IIf(Sum(IIf(DatePart("m",[PaymentDate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])=7,1,0)))

I'm not seeing how to replace a none date field to be counted and get running totals each month when the date field looks to be significant here in determining each month of the fiscal year.

Where would the MembershipID field go when it appears, in this, a date field is used for the cumulating / running totals for each month?

It is important to get the totals of the memberships (MembershipID) for all of the queries.


I did find out:
1. Queries 01-05 and 08 memberships is based off of the PaymentDate.
2. Queries 06-07 and 10-11 memberships have no PaymentDates and they do not want to use the CreatedDate for these queries. The count is based on running the count of the memberships on the last day of the current month or the 1st day of the next month. Storing that number then doing the same the next month to add the prior month for the cumulated total.

I'll be creating a temptable to store the MemberTypeIDs, year, and months with the counts in each month. This will have to be linked to the queries 06-07 and 10-11 to get the prior months number and add that to get the cumulative total.

[aellistechsupport]

Ok, I'm uploading an updated db with the changes from what I've found out.

1. The first 5 basic queries (NOT the two ending with "Inception") and 08_FOFS - the counts are based on PaymentDate for the fiscal year monthly columns. I think it's OK with counting and doing the running totals by the PaymentDate since all of theses memberships are based on PaymentDate. I checked on a few records from the query using your running totals to a cross-tab query and the numbers seem ok for these 6 queries.


2. The two queries ending with "Inception", 06_SynchroAndTheaterOnIce, and query 11_BasicSkillsProgram - these queries are NOT based on PaymentDate. It is based on "live date". Date the queries are ran to get the total counts for the month. That number is saved to be used next month with the next month's number which is ran on the last day of the month or the 1st day of the next month. And added to the prior month's stored number for the running total.


3. For queries 06 and 11, it's for the current fiscal year regardless of when the records were created.

This is what I'm trying to do to get the monthly totals.

Please see query: "Copy of 11_BasicSkillsProgram". Trying to do something like this.

Here is the result:

MemberGroup	Status	07	08	09	10	11	NoOfMbrshp	Dec	01	02	03	04	05	06
	Active w/ 0 Mbs	556	501	381	280	254		254
	In Active	81	81	80	78	78		78
Basic Skills Program	Active	314	382	509	616	643	13	656

I removed the "NoOfMbrshp" column. That was for display only here.
I entered 0 for Dec-Mar since those counts were not saved and I don't know how to get those numbers now since they have passed.
Now, I'm not sure why it's not cumulating after I changed it to current month?

MemberGroup	Status	07	08	09	10	11	12	01	02	03	Apr	05	06
Basic Skills Program	Active	314	382	509	616	643	0	0	0	0	13	0	0
	Active w/ 0 Mbs	556	501	381	280	254	0	0	0	0	0	0	0
	In Active	81	81	80	78	78	0	0	0	0	0	0	0

And not sure how to handle future months and future years and the months?


4. For the two queries ending with "Inception". Not sure how to get the running totals from time of "Inception". Which I would say is when the members were created. So since then and until the month these queries are ran for the current fiscal year are the running counts. See above on my idea for queries 06 and 11. Could use that process but run into the same issue of how the future years would be handled? What would you recommend?

5. Still trying to find out about queries 10s. I don't see a PaymentDate for these clubs so not sure how they are determining who's paid or not without a PaymentDate.

[aellistechsupport]

Ok for the Member Clubs, queries 10s, they are considering all the membership records with an expired EndDate as Not Paid club members. Although trying to figure out how to see if the same record doesn't have an unexpired EndDate and figure out they are expired?

SELECT "Member Clubs" AS MemberGroup, IIf([EndDate]<Date(),"Regular Not Paid","Regular") AS MemberType, Count(dbo_v030mbrshp00ClubsProgram.MembershipNumber) AS NoOfMbrshp
FROM dbo_v030mbrshp00ClubsProgram
WHERE (((dbo_v030mbrshp00ClubsProgram.MemberTypeID)=20 Or (dbo_v030mbrshp00ClubsProgram.MemberTypeID)=21))
GROUP BY "Member Clubs", IIf([EndDate]<Date(),"Regular Not Paid","Regular");

And I also need to do with this is the same thing as queries 06 and 11, since they want number of members at the month the query is ran ("live date") data set.

Do you know of a way to capture the info to use it then the way I was thinking with the temptable?

Please disregard the last attachment and look at this one.

[aellistechsupport]

Solved with:
https://www.accessforums.net/queries...nth-43364.html