DSum for a month to month rolling / running totals and within a set date range?

[aellistechsupport]

I am not sure how to change these codes to show the counts on another field. "MembershipNumber". So Count(MembershipNumber) and then a Sum from prior month and next month.




A rolling / running total each month for a duration of 13 months but showing 12 months of rolling totals.
It's the fiscal year Jul - Jun but because payments are received starting 1st of June, last July has last June's total.
Current year's June should not show any numbers yet.


To be sure it's also counting and doing a running total correctly how do I change it to count on MembershipNumber field in the code? But has to be in the date range. For that month.

Jul: IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=6 And DatePart("m",[paymentdate])<=7,1,0)))

This counts the payment date for Jun and Jul


This counts Jun, Jul and Aug as the "running total"

Aug: 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)))

based on this criteria it is displaying last year:

Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1) Or Is Null

However, Jun for this year is this:
and showing a total and it should NOT because Jun this year isn't here yet.

Jun: IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0)))

Result:

MemberType    Jul    Aug    Sep    Oct    Nov    Dec    Jan    Feb    Mar    Apr    May    Jun
First Family    23612    25925    27572    29345    30193    30523    30904    31237    31446    31554        31554
Subsequent Family    11168    12378    13100    13971    14415    14592    14785    14976    15129    15217        15217
Introductory    1739    2258    2691    3198    3509    3675    3949    4187    4419    4523        4523

See how it's showing this year's Jun with data but current year's Jun isn't here yet.

I hope I explained this well enough.

1. Need to show rolling totals of the total COUNT each month but on MembershipNumber field NOT on the PaymentDate field.
2. Code will be for 13 months but displaying 12 months in the columns. Monthly columns of the total count on MembershipNumber but a running total in subsequent / following / rolling months.

1) See 1st code for beginning month of the fiscal year. It should include the total count from the prior month of June in the July column with the total count for July.
2) See 2nd code for the 2nd month of the fiscal year, Aug.
3) See 3rd code for limiting to the 13 months.
4) See 4th code for the last month of the fiscal year. Since June hasn't come yet there should be no data but the code is showing there is.
5) See 5th code for the Results. See how June is showing data which is supposed to be the current year June.

[rpeare]

can you upload a fresh copy of your database, you've posted several threads and I don't recall what the original file was nor do I likely have something resembling your original example database.

[June7]

Is this same topic https://www.accessforums.net/queries...nth-43364.html

[rpeare]

No it's a different issue I think, he's got the net amounts now, but it's not selecting the correct date range to perform the calculation.

[aellistechsupport]

No it's a different issue I think, he's got the net amounts now, but it's not selecting the correct date range to perform the calculation.

Yes, I am still having an issue with the current year's Jun column.

For some reason on the following statement, it is showing totals for this June. I don't understand why.

Jun: IIf(Sum(IIf(DatePart("m",[PaymentDate])=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0)))

Based on the PaymentDate range from last June to current June I am getting only up until April, which is right.
When I sort on the PaymentDate in detail this is what I see as the most recent date data: Which is what needs to happen. HOWEVER

MemberGroup	MemberType													PaymentDate
Regular Member	First Family										2			4/24/2014
Regular Member	Subsequent Family										12			4/24/2014
Regular Member	Subsequent Family										8			4/23/2014
Regular Member	First Family										10			4/23/2014
Regular Member	Introductory										3			4/23/2014

Result:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	23612	25925	27572	29345	30193	30523	30904	31237	31446	31557		31557
Regular Member	Subsequent Family	11168	12378	13100	13971	14415	14592	14785	14976	15129	15230		15230
Regular Member	Introductory	1739	2258	2691	3198	3509	3675	3949	4187	4419	4523		4523

Why is it showing the same numbers in Apr in Jun?

[rpeare]

Is your most recent database on the other thread or do you have something more current to upload.

[aellistechsupport]

I'll upload a current one.

thank you.

[aellistechsupport]

Memberships.zip

I only made a copy of one views as a table in Access that is related to the issue I'm having due to the size limit on the attachments.

Please take a look at the query 01_RegMembers

[rpeare]

When I run your query 01_RegMembers this is what I get:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	8753	11066	12713	14485	15333	15663	16044	16377	16586
Regular Member	Introductory	820	1339	1772	2279	2590	2756	3030	3268	3500
Regular Member	Subsequent Family	4515	5725	6447	7318	7762	7939	8132	8323	8476

What should it say if this is wrong? As far as I can tell your criteria are functioning the way they should (for instance the calculation of payment date being between the start of the fiscal year and one month prior to the current month)

[aellistechsupport]

weird.

I get this EXCEPT the last month column (Jun) should be blank. but it's repeating Apr

Result:

MemberGroup	MemberType	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Regular Member	First Family	23612	25925	27572	29345	30193	30523	30904	31237	31446	31557		31557
Regular Member	Subsequent Family	11168	12378	13100	13971	14415	14592	14785	14976	15129	15230		15230
Regular Member	Introductory	1739	2258	2691	3198	3509	3675	3949	4187	4419	4523		4523

SELECT dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, IIf(Sum(IIf(DatePart("m",[paymentdate])>=6 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])>=6 And 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])>=6 And 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])>=6 And 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])>=6 And 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])>=6 And 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])>=6 And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
FROM dbo_v030mbrshp01PdMembers
WHERE (((dbo_v030mbrshp01PdMembers.PaymentDate) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),6,1)) And DateSerial(Year(Date()),6,1)) AND ((dbo_v030mbrshp01PdMembers.MemberTypeID)=1 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=2 Or (dbo_v030mbrshp01PdMembers.MemberTypeID)=4))
GROUP BY dbo_v030mbrshp01PdMembers.MemberGroup, dbo_v030mbrshp01PdMembers.MemberType, dbo_v030mbrshp01PdMembers.MemberTypeID
ORDER BY dbo_v030mbrshp01PdMembers.MemberTypeID;

[rpeare]

The query code you appended is referencing a table that is not in your example data.

[aellistechsupport]

? It's not appending to anything.

the dbo_v030mbrshp01PdMembers is dbo.v030mrshp01PdMembers.

I forgot to rename the dbo table after doing the make table.

[rpeare]

I said the query CODE you appended (pasted) is referencing a table that is not in your example database

the query CODE in post 10 is referencing dbo_v030mbrshp01PdMembers, there is no such table or query in the example database you uploaded.

[aellistechsupport]

Yes,

sorry about that.

the dbo_v030mbrshp01PdMembers is dbo.v030mrshp01PdMembers.

I forgot to rename the dbo table after doing the make table.

If you want I can re-upload the db with the correct name or if you don't mind, change the preceding characters from dbo_ to dbo. of the table that is in that file

[rpeare]

I'm not being clear... there is no query that is immediately obvious in your most recently uploaded database that comes close to matching this naming convention. Here are your tables:

dbo_v030mbrshp00
dbo_v030mbrshp00ClubsProgram
dbo_v030mbrshp00Collegiates
dbo_v030mbrshp00Teams

There are no hidden tables, no queries that having naming conventions that are similar to what is being asked for in the query, so which table do you propose is the misnamed one?