Exclude when the current month is the month to exclude?

[aellistechsupport]

I was thinking that <>Month(Date()) would work but now that we're in May it is not.
I had to remove that criteria for 2 reports to match.

Then tried <>Month(Date()) and Not In (5, 6), didn't work ...

Here's the situation the best way I can explain it. Attached to see what I'm trying to explain. 02_Collegiate.zip

1. Fiscal year is Jul - Jun. So if we're talking about the current fiscal year it is Jul 2014 - Jun 2015. After Jun 2015, it's Jul 2015 - Jun 2015, the next fiscal year.
2. This query is showing or is to show, in the fiscal monthly buckets / columns of how many memberships were / are purchased.
3. These particular memberships however are good for up to 4 years with the membership season ending from the current year 2015 through 4 years from this year, ending in 2018. Year 1 = 2015, Year 2 = 2016, Year 3 = 2017, and year 4 ending in 2018 (current can still be purchased).
4. Memberships can be bought starting in May for the future Season. So all May and Jun purchases are rolled into Jul, the 1st month of the fiscal year.

All purchases (PaymentDate) WITH a Season in the future (EndDate) is the parameter.

All May and Jun purchases of PAST purchases are supposed to be rolled in Jul, the 1st month of the fiscal year.

There's an issue when attempting to separate out the May and Jun purchases since it's looking at it in the above format.
If you look at the details you'll see that, according to date range parameters, the purchases (PaymentDate) are less than the EndDate it is counted.



Since I'm having difficulty trying to explain this because I'm not sure how to handle, I'm hoping laying out the points will help in trying to figure out the best way to handle this issue.

I think it's counting the May and June twice, once into the Jul column then when May and Jun come around. Which is incorrect account of how many memberships.

[Rawb]

You have "And 7" in your IIf() statements for Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar, Apr, and May, which will always return True (since you're not comparing the 7 to anything else). I'm guessing that's not what you want to be doing.

Also, on Jul and Jun, you have logic that compares the month and accepts two values but then immediately compares it to one value...

Example Jul breakdown:

IIf(
    (DatePart("m",[PaymentDate])>=5 Or DatePart("m",[PaymentDate])<=7)
    And DatePart("m",[paymentdate])<=7,
    1,0
)

This equates to:

IIf(
    (X >= 5 Or X =< 7) And X =< 7,
    1,0
)

You do the same thing with Jun, just using months 6 and 7 instead of 5 and 7. If you're going to only accept a month of 7 as a valid answer in your IIf(), then you can just use:

IIf(DatePart("m",[paymentdate])<=7,1,0)

It's pretty late for me here, but I'll take another look at your SQL Query in the morning and see if I can come up with something that might be a little more what you want. In the meantime, are you saying that there will never be any May/June purchases except for the current Fiscal year, or for the calendar year (i.e.: If it's November, should the report still show purchases for May/June or should they all be rolled over into July by that point)?

[aellistechsupport]

The 1st fiscal month Jul needs to include the cumulative total for May, Jun, Jul, then in Aug it's the total in Jul which is from 5-8, then 5-9 for Sep and so on.

for example you purchased a membership in May 2011 ending in 2015, then you purchased a membership in May 2012 ending in 2015.
the May 2011 shows in the 1st Jul column for Season 2015 and the May 2012 ending in 2015 in the May column, the 2nd to the last month of that fiscal year.

[Rawb]

I don't think I understand what you mean. Assuming that the following number of purchases were made during each of these months, what should the Query be returning?

Month	Purchases
May 2011	10
Jun 2011	15
Jul 2011	12
Aug 2011	14
Sep 2011	19
Oct 2011	16
Nov 2011	20
Dec 2011	21
Jan 2012	17
Feb 2012	11
Mar 2012	18
Apr 2012	13
May 2012	22

[aellistechsupport]

I think that it should be:
Jul = 37 (May 2011 - Jul 2011) purchases
Aug = 37 from Jul + 14 from Aug 2011 total: 51
and so on where
Apr = 186
May = 186+22 total: 208
and let's add the last month of the fiscal year
Jun and say it had Jun 2012 10 purchases making the cumulative total through Jun: 218

I think currently it is showing the May 2012 of 22 purchases in the first column of Jul with the May 2011 purchases.

See attached: Collegiates.zip

Look at query4 and query5 (detail of query4)

[aellistechsupport]

Left out that's only True IF the Season is ending in the SAME year

If the May 2012 membership ends in 2015, otherwise that total would show in the 1st column Jul for Season 2016.

Example, this is also showing to count in the May and Jun columns of the fiscal year instead of only showing in Jul.

Season	YearPaid	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	PaymentDate	EndDate	MembershipNumber
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/15/2012	6/30/2015	1144627
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/16/2012	6/30/2015	1005705
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	5/16/2012	6/30/2015	1099838
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	6/28/2012	6/30/2015	1131963
2015	2012	1	1	1	1	1	1	1	1	1	1	1	1	7/19/2012	6/30/2015	1391914

[aellistechsupport]

It seems like some sort of an Iif statement should work but I'm not sure how to apply it?

[Rawb]

Sorry for the lack of an update. I should know better than to promise something on a Monday morning by now

Anyway, I've been looking at the problem and have it mostly-kinda-sorta resolved: I can get accurate number for every month except June, the last month in the Fiscal year. For some reason it wants to add July's numbers (the first month of that same Fiscal year) to the result twice instead of just once. Also, the results I have are row-based instead of column-based.

Once I get the June/math issue worked out though, I should be able to convert it to columns for you.

[aellistechsupport]

THANK YOU! I'm excited!
And glad you understand what I was explaining lol

[aellistechsupport]

it should EXCLUDE those memberships shown in the table example I last posted in the last two columns in May and Jun.
As you can see the PaymentDate ends in 2012, these are counted with the 7/19/2012 payment in the Jul column of the 1st month of the fiscal year with season ending (EndDate) of 2015.

But they are showing as being counted again in the May and Jun columns and they should NOT UNLESS the EndDate is 2016 but since it's 2015, it's already counted at the beginning.

[Rawb]

Apparently I missed a couple of posts you made before now that changed the rules significantly from what I understood them to be. At this point, I can't say that I understand what you're trying to do at all.

I'm sorry, but I don't think I'll be able to help you.

[aellistechsupport]

I'm sorry, there's no rule change. Meaning nothing new has been added.

And only answered your question from your example as clear as I can.

[Rawb]

Here's what I had so far. For readability, I split it into 2 Queries: qryFiscalPayments_01 and qryFiscalPayments_02.

qryFiscalPayments_01 does 2 things:

• It creates two Columns called FiscalMonth and FiscalYear which can be used instead of having to manually shift everything around.
• It totals the number of Payments made during the month. This is not a running sum and ONLY includes the payments made in that month.

qryFiscalPayments_02 just takes _01 and uses it to create a running sum for each Fiscal year. The Fiscal years include totals from the last two months of the previous Fiscal year.

I was also playing with a Crosstab Query (qryFiscalPayments_Pivot) but was unable to tie it to either of the above Queries due to MS Access wackiness.

[aellistechsupport]

Hi, thanks for the attempt ... but it's doing the same thing I already have as far as the results are concerned.

In the same db you worked with, see Query1.

So change Query1 to this:

SELECT [02_Collegiate_Details].Season, Count([02_Collegiate_Details].EndDate) AS [Total Of EndDate]
FROM 02_Collegiate_Details
WHERE (((Month([PaymentDate]))<>Month(Date())))
GROUP BY [02_Collegiate_Details].Season;

Which results to this:

Season	Total Of EndDate
2015	949
2016	887
2017	935
2018	1055

Grand total: 3826

I think I was making it more complicated. So I'm looking for how to exclude 5 and 6 (May and Jun) from the above code.
And please take a look at Query4 and get rid of the YearPaid field.

Which will show: which the grand total in the Apr column is 3839

Season	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
2015	549	660	731	814	861	889	917	935	949	954	954	954
2016	520	617	716	795	822	836	856	874	887	889	889	889
2017	537	622	693	813	855	869	901	921	931	941	941	941
2018	628	721	826	888	936	972	1020	1042	1050	1055	1055	1055

I think this works as the grand total in Apr column is in fact 3839.

HOWEVER the results are wrong. It is showing only a grand total of 3826 instead of 3839 from the prior example above. It's excluding the 13 May payments and it should NOT because they are and need to stay included in the totals from the cumulative total from the 1st column Jul (which includes May-Jul).

These 13 May records older than this year.

Season	Total Of EndDate	MembershipNumber	PaymentDate
2015	1	1005705	5/16/2012
2015	1	1099838	5/16/2012
2015	1	1144627	5/15/2012
2015	1	1252333	5/28/2013
2015	1	6436112	5/7/2013
2016	1	6302859	5/1/2013
2016	1	8919316	5/1/2013
2017	1	1060155	5/2/2014
2017	1	1068751	5/7/2014
2017	1	1276092	5/9/2014
2017	1	1530334	5/25/2014
2017	1	6512612	5/15/2014
2017	1	8746552	5/3/2014

Apr shows up to Mar because the data of up to end of Mar isn't available until Apr.
So in the May column data up to Apr should show and so on. Since I'm reporting in May the May and Jun data will equal what's showing in Apr until May is over AND only If there is May 2015 data, the last Season 2018 would change from 1055 but nothing else, those other Seasons, 2015-2017, would remain the same.
And NOT include the prior May data.

So last month of Apr but in Mar column it excluded the last 5 records in bold red. 1050. This month it shows in the Apr column 1055.

Season	Total Of EndDate	MembershipNumber	PaymentDate
2015	1	942501	4/1/2012
2015	1	1395682	4/6/2012
2015	1	1486428	4/8/2012
2015	1	6007168	4/17/2012
2015	1	8762208	4/27/2012
2016	1	924553	4/30/2013
2016	1	1040804	4/20/2013
2017	1	1063631	4/23/2014
2017	1	1063633	4/23/2014
2017	1	1063707	4/23/2014
2017	1	1063708	4/23/2014
2017	1	1126299	4/13/2014
2017	1	1438341	4/11/2014
2017	1	1482073	4/3/2014
2017	1	1537156	4/12/2014
2017	1	6303630	4/3/2014
2017	1	6323641	4/14/2014
2018	1	1191344	4/16/2015
2018	1	1314460	4/29/2015
2018	1	1497703	4/2/2015
2018	1	1518957	4/7/2015
2018	1	6519193	4/14/2015

Best way I know how to explain along with the data details to show it.

[aellistechsupport]

So how to INCLUDE the month of May or June data when the current month is May or if the current month is June but all other months exclude the "current" month?

I'm working with

  <>Month(Date())

to exclude current month, however I need ALL months when the current month is May or current month is June. Meaning only in the month of May or month of June I need the <>Month(Date()) to NOT take affect.

<>Month(Date()) and (Month(Date()) Not In (5,6))

does not work, it results in Null

<>Month(Date()) and (Month(Date()) In (5,6))

does not work, it also results in Null

There has to be a way to do this, anyone know the correct formula?