Results 1 to 4 of 4
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    Overlapping Dates in Totals?

    I am trying to summarize based on payment date range and season end dates and have overlapping months.



    1. Fiscal year is July - June
    2. Payments for the next fiscal year start in May
    3. Need total payments received each month for each season
    4. Season is 4 years long
    5. May and June payments go into the the 1st month column in July with July payments. So 3 months of payments, May, Jun, and Jul, the total goes into Jul.
    6. Aug payments go into Aug, Sep payments into Sep and so on until the 12th month, last fiscal month of Jun.
    7. As you can see the over lapping months on are May and Jun


    This provides 4 years of payments
    Code:
    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()),Month(Date()),0),DateSerial(Year(Date()),Month(Date()),0))


    This provides 4 years of seasons
    Code:
    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))

    The above is in a query named "02_Collegiate_Details" the one below is referencing in order to do the summarized totals:

    Then in another query this to see per season per fiscal month total payments:
    Code:
    SELECT  [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season,  IIf(Sum(IIf((DatePart("m",[PaymentDate])>=5 Or  DatePart("m",[PaymentDate])<=7) And  DatePart("m",[paymentdate])<=7,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5  And DatePart("m",[PaymentDate])<=7,1,0))) AS Jul,  Sum(IIf(DatePart("m",[paymentdate])=8 And  DatePart("m",[paymentdate])=8,1,0)) AS Aug,  Sum(IIf(DatePart("m",[paymentdate])=9 And  DatePart("m",[paymentdate])=9,1,0)) AS Sep,  Sum(IIf(DatePart("m",[paymentdate])=10 And  DatePart("m",[paymentdate])=10,1,0)) AS Oct,  Sum(IIf(DatePart("m",[paymentdate])=11 And  DatePart("m",[paymentdate])=11,1,0)) AS Nov,  Sum(IIf(DatePart("m",[paymentdate])=12 And  DatePart("m",[paymentdate])=12,1,0)) AS [Dec],  Sum(IIf(DatePart("m",[paymentdate])=1 And  DatePart("m",[paymentdate])=1,1,0)) AS Jan,  Sum(IIf(DatePart("m",[paymentdate])=2 And  DatePart("m",[paymentdate])=2,1,0)) AS Feb,  Sum(IIf(DatePart("m",[paymentdate])=3 And  DatePart("m",[paymentdate])=3,1,0)) AS Mar,  Sum(IIf(DatePart("m",[paymentdate])=4 And  DatePart("m",[paymentdate])=4,1,0)) AS Apr,  IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or  DatePart("m",[PaymentDate])<=5,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5  And 7 Or DatePart("m",[PaymentDate])<=5,1,0))) AS May,  IIf(Sum(IIf(DatePart("m",[PaymentDate])>=5 And 7 Or  DatePart("m",[PaymentDate])<=6,1,0))=0,Null,Sum(IIf(DatePart("m",[PaymentDate])>=5  And 7 Or DatePart("m",[PaymentDate])<=6,1,0))) AS Jun
    FROM 02_Collegiate_Details
    GROUP BY [02_Collegiate_Details].MemberType, [02_Collegiate_Details].Season;


    When I get to columns May and Jun, I am unsure how to change the existing one (it was doing a cumulative total but need to change it for individual months EXCEPT at the beginning of the fiscal year where Jul combines May-Jul payments for Jul

    Does anyone know how it can be modified so that upcoming May and Jun are NOT totaling the last May and Jun as it is doing in the 1st column of Jul?



    MemberType Season Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Collegiate 2016 520 97 99 79 27 14 20 18 13 2 889 889
    Collegiate 2017 537 85 72 121 42 14 32 20 10 10 943 943
    Collegiate 2018 638 94 108 61 48 36 48 22 8 5 1068 1068
    Collegiate 2019 696 83 92 99 46 16 20 0 0 0 1052 1052

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by orange View Post
    Not sure why this is necessary at this point. I've found help differently or one vs. the other.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The point I was making was to advise others that spending a lot of time resolving/assisting/researching your post MAY be pointless because it MAY have been resolved elsewhere.

    When you cross post, just advise readers that you have done so. Cross posting isn't wrong, but have the courtesy to let readers know.

    Here's the rationale.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Fields Overlapping
    By roarcrm in forum Forms
    Replies: 2
    Last Post: 04-13-2015, 10:34 AM
  2. How to query for overlapping dates
    By DavidZ in forum Queries
    Replies: 7
    Last Post: 03-10-2015, 01:54 PM
  3. Getting Totals between 2 Dates
    By raiderman69 in forum Queries
    Replies: 3
    Last Post: 04-25-2014, 12:15 AM
  4. Replies: 3
    Last Post: 06-27-2013, 10:18 AM
  5. Overlapping Subreports
    By gopherking in forum Reports
    Replies: 3
    Last Post: 11-09-2011, 07:07 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums