Fiscal Year to include same months but for different payment years?

[aellistechsupport]

cumulative months for the fiscal year. where the fiscal year is July - June. So now that it's August the current fiscal year is July 2014 through June 2015.

WHERE the month of July 2014 should include invoices from May and June AND July 2014 for the Jul column.
And the Aug column should be May-Aug and so on ending with the last month column of Jun which is May 2014 - June 2015.

SELECT dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear AS Season, IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7) And DatePart("yyyy",[paymentdate])=(DatePart("yyyy",Date())-1),1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=5 And 7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 And DatePart("m",[PaymentDate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5 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_v030mbrshp02Collegiates
WHERE (((dbo_v030mbrshp02Collegiates.MemberTypeID)=3) AND ((dbo_v030mbrshp02Collegiates.EndDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+1,6,30)) And IIf(Month(Date())<=7,DateSerial(Year(Date())+3,6,30),DateSerial(Year(Date())+4,6,30))) AND ((dbo_v030mbrshp02Collegiates.PaymentDate) Between IIf(Month(Date())<=7,DateSerial(Year(Date())-4,5,1),DateSerial(Year(Date())-3,5,1)) And IIf(Month(Date())<=7,DateSerial(Year(Date()),6,30),DateSerial(Year(Date())+3,6,30))))
GROUP BY dbo_v030mbrshp02Collegiates.MemberType, dbo_v030mbrshp02Collegiates.EndYear;

However this is resulting this:

MemberType	Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Collegiate	2015	553	664	735	818	860	888	916	934	948	953	958	958
Collegiate	2016	523	620	721	800	825	839	859	877	890	892	894	894
Collegiate	2017	539	624	695	816	852	866	898	918	928	938	944	944
Collegiate	2018		648	648	648	648	648	648					648

the last row with the Season ending in 2018, the 648 in Jun should show in the first column July since July should be May-Jul 2014 invoices.
The details show it's 642 invoices from May-July 2014:


And so far for August 2014 it's 12 records.
So Jul column should be May-Jul 2014 of 642 showing
then Aug column 642 + 12 = 654

as you can see in the first summary shows Null in the Jul column and 648 in Aug?

MemberType	Season	EndDate	PaymentDate
Collegiate	2018	6/30/2018	8/2/2014
Collegiate	2018	6/30/2018	8/2/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014
Collegiate	2018	6/30/2018	8/1/2014

How can I change the parameters to correct the problem that is showing?

I'm attaching the database.
02_Collegiate and 04_IndCollegiate queries are the ones at issue.MembershipsReporting.zip

[June7]

As stated in your other thread https://www.accessforums.net/queries...tml#post239789, that sounds like a running sum. Running sum is not easy on form or query. Common topic. Here is one recent: https://www.accessforums.net/queries...ery-45841.html

[aellistechsupport]

It's more involved or different I suppose.

I'm trying to figure out HOW to account for prior year same months data to show in the monthly columns (buckets) in the 12 month buckets.

Running sum is essentially working. The issue here is showing the correct months for the year into the correct columns.

[June7]

Hard to read that query.

Isn't the year criteria in the GROUP BY clause accomplishing that?

[aellistechsupport]

I've uploaded the database and the two queries at issues.

That is what I thought BUT it's not doing it right.

I think it's because of the date parameter in each column (month bucket) in handling a prior year month with the current year month that are equal.

May and Jun from last year and May and Jun from this year.

So I think that's where the issue is and need help fixing that to handle it as it needs to be.

[June7]

The expressions for the months have structure I don't understand. I am surprised the query runs at all. The part in red makes no sense to me.

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

[aellistechsupport]

I'm not sure either actually.
I got that from somewhere else.

Guess the question is what IS the correct syntax rather than trying to understand something that isn't working properly?

I need the Jun column to include total counts from May2014 - Jun2015

With the first column Jul to include total counts from May2014-Jul2014
Aug column to include total counts from May2014-Aug2014, etc ... until that last column Jun.
As noted, needs to include total counts from May2014-Jun2015

Where the month of May and Jun are repeated. Ones from 2014 and ones from 2015.

[June7]

I have never needed a running sum and trying to do in query without considering grouping is tough enough. I attempted option 2 in http://support.microsoft.com/kb/290136 but that is not working for me.

I am afraid this is over my head. Review another recent thread trying running sum https://www.accessforums.net/queries...41/index2.html

[aellistechsupport]

Will do. Why I started a new thread instead mucking up that other one.

Hopefully someone soon can help with this.

tnx though

[aellistechsupport]

Anyone out there might know and can offer some help on this?
Thank you!

[ItsMe]

Not looking at the entire thread and only responding to the Title, I approach these issues by creating tables that assign integers to Months, Days, and Weekdays. My tblMonths will have 12 records. My tblDays will have 31 records. My tblWeekdays will have many records that represent a range of dates. To create tblWeekdays, I use VBA and the Weekday function, storing calculated integer results in the table.

I use these tables, sometimes one sometimes all, to determine a given period for a date. I use the integer values in the tables to determine the period of a date variable. Once I have the period numbers for the date variables, I can do other things.

[burrina]

Here are 2 examples that should prove helpful. One to get the Fiscal Year and the other to use a Running Sum. Hope it Helps!


Type the following two procedures:

Function GetFiscalYear(ByVal x As Variant)
If x < DateSerial(Year(x), FMonthStart, FDayStart) Then
GetFiscalYear = Year(x) - FYearOffset - 1
Else
GetFiscalYear = Year(x) - FYearOffset
End If
End Function

Function GetFiscalMonth(ByVal x As Variant)
Dim m
m = Month(x) - FMonthStart + 1
If Day(x) < FDayStart Then m = m - 1
If m < 1 Then m = m + 12
GetFiscalMonth = m
End Function

Option Compare Database


Function RunSum(F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'************************************************* **********
' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.
' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'************************************************* **********
Dim rs As DAO.Recordset
Dim Result


On Error GoTo Err_RunSum


' Get the form Recordset.
Set rs = F.RecordsetClone


' Find the current record.
Select Case rs.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE
rs.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
rs.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_Text
rs.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select


' Compute the running sum.
Do Until rs.BOF
Result = Result + rs(FieldToSum)


' Move to the previous record.
rs.MovePrevious
Loop


Bye_RunSum:
RunSum = Result
Exit Function


Err_RunSum:
Resume Bye_RunSum


End Function

Good Luck!

[aellistechsupport]

You know I tried the GetFiscalYear function but it really does not achieve what I'm stuck with.

The issue is that I need to display a fiscal year (which is 12 months) HOWEVER the totals is for 15 months.

AND when the first fiscal month of the year changes to the next year it needs to start over, hence the if month is July then calculate, etc ....

I have achieved what I need but ONLY when I did not need to show the end dates (Seasons).

That's the issue that I have is to show the payments for 4 years of when the seasons end.