Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 53
  1. #16
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Sorry,
    For some reason in this query it doesn't appear to be doing the cumulative totals?

    MemberType SeasonEnding Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Collegiate 2014
    1 1 2 2 2 2




    Collegiate 2015 1 2 2 2 2 2 2




    Collegiate 2016 4 5 6 6 6 6 6




    Collegiate 2017 242 324 393 511 549 565 597 617 627





    Code:
    SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, IIf(Sum(IIf(DatePart("m",[paymentdate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=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])>=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])>=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])>=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])>=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])>=7 Or DatePart("m",[paymentdate])<=6,1,0))) AS Jun
    FROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
    WHERE (((attribute_PersonMembership.PaymentDate) Between DateAdd("yyyy",-1,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),Month(Date()-1),0) Or (attribute_PersonMembership.PaymentDate) Is Null) AND ((lookup_MemberTypes.Id)=3))
    GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy")
    HAVING (((Format([EndDate],"yyyy"))>=2014))
    ORDER BY Format([EndDate],"yyyy");
    I don't get it?

  2. #17
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ok, sorry, not sure why this query isn't working?

    Code:
    SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, IIf(Sum(IIf(DatePart("m",[paymentdate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=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])>=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])>=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])>=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])>=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])>=7 Or DatePart("m",[paymentdate])<=6,1,0))) AS Jun
    FROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
    WHERE (((attribute_PersonMembership.PaymentDate) Between DateAdd("yyyy",-4,DateSerial(Year(Date()),7,1)) And DateSerial(Year(Date()),Month(Date()-1),0) Or (attribute_PersonMembership.PaymentDate) Is Null) AND ((lookup_MemberTypes.Id)=3))
    GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy")
    HAVING (((Format([EndDate],"yyyy"))>=2014))
    ORDER BY Format([EndDate],"yyyy");
    MemberType SeasonEnding Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Collegiate 2014 284 380 459 562 611 627 641 653 666 672 676 686
    Collegiate 2015 276 379 445 526 572 600 628 646 660 665 668 935
    Collegiate 2016 240 334 434 510 536 547 565 582 594 596 598 878
    Collegiate 2017 242 324 393 511 550 566 598 618 628

    918

    This one needs to show the cumulative totals for the months during the fiscal year for the 4 years (seasons)

  3. #18
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What are you trying to accomplish with this query? You are using a mix of dates (end date and payment date) in your query which is probably why you're some weird results. When I run your second query on the database you uploaded earlier I get almost no data for 2014/2015, can I safely assume you have more data in your copy of the database?

    Look at this query:

    Code:
    SELECT DatePart("yyyy",[enddate]) AS Season, DatePart("yyyy",[paymentdate]) & Right("0" & DatePart("m",[paymentdate]),2) AS PayMonth, Count(attribute_PersonMembership.PaymentDate) AS PayCount
    FROM attribute_PersonMembership
    GROUP BY DatePart("yyyy",[enddate]), DatePart("yyyy",[paymentdate]) & Right("0" & DatePart("m",[paymentdate]),2)
    HAVING (((DatePart("yyyy",[enddate]))=2017))
    ORDER BY DatePart("yyyy",[paymentdate]) & Right("0" & DatePart("m",[paymentdate]),2) DESC
    This query is showing you how payment has been received for the 2017 season. You have payment dates ranging from November of 2011 through april of 2014, so the same matrix you had will not work as the payment for a particular season can be spread across several years.

    You might be better served by looking at perhaps the year of the payment date vs the year of season that payment was for but I don't know what your goal with this query is.

  4. #19
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Yes, I believe that is why.

    The thing is they need to see how many memberships were generated in each month for when the memberships were paid. RE: PaymentDate
    But for this query they also want to see it for each season which is the StartDate and EndDate that provides the membership duration RE: Season it's in.

    I will be looking through the details to see why the season ending 2017 has payments prior to 2014 as to why there's counts for Jun this year?
    Thank you for your help. If this info helps any and if you have an idea on the logic being used if the current one isn't right or ?, that would be appreciated.
    I'm thinking it's not the query? but the data set?

    Collegiate 2017 242
    324 393 511 550 566 598 618 628


    918

  5. #20
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    ok so there aren't any records for this set in June 2014.
    So I'm not sure why there is 918 showing in the June column.

    What do you think?

  6. #21
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This Query would show you by season, which year the payments were made regardless of when the payments were made

    Code:
    SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, DatePart("yyyy",[paymentdate]) AS PaymentYear, IIf(Sum(IIf(DatePart("m",[paymentdate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=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])>=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])>=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])>=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])>=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])>=7 Or DatePart("m",[paymentdate])<=6,1,0))) AS Jun
    FROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
    WHERE (((lookup_MemberTypes.Id)=3))
    GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate])
    HAVING (((Format([EndDate],"yyyy"))>=2014))
    ORDER BY Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate])
    The reason you're seeing a record in june is that you are NOT separating the payment year from the year of the season. Look at the query above.

  7. #22
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ok, so how do I get the counts for month of the fiscal year based on PaymentDate but also showing for each season end?

  8. #23
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Also,
    Would this tell me Active memberships that haven't paid as well as Active ones who have?
    It's not returning any counts for the Active Not Paid?

    Where Status = 3 is "Active" [this table is not in the sample db however note Status = 3 is Active]

    Code:
    MemberStatus: IIf([PaymentDate] Is Null And [Status]=3 or Null,"Active Not Paid","Active")
    MemberGroup MemberType MemberStatus Jul Aug Sep Oct Nov Dec Jan Feb Mar
    Regular Member First Family Active Not Paid








    Regular Member First Family Active 8801 11122 12771 14546 15395 15725 16107 16440 16649
    Regular Member Introductory Active Not Paid








    Regular Member Introductory Active 823 1344 1777 2284 2596 2762 3036 3274 3506
    Regular Member Subsequent Family Active Not Paid








    Regular Member Subsequent Family Active 4540 5768 6499 7374 7828 8013 8207 8398 8555

    Since it's important to know there's no date in PaymentDate as that's what'll tell me membership was not paid.

  9. #24
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok, so how do I get the counts for month of the fiscal year based on PaymentDate but also showing for each season end?
    I don't understand what you're asking

    Fiscal year based on payment?
    Fiscal year based on end date?
    What are the limits of your fiscal year?
    Is it the same as the calendar year?

    The query I gave you shows the PAYMENTS by month for every SEASON calendar year for every season past 2014. In the case of the 2017 SEASON (based on end date) you have payments (Based on PAYMENTDATE) spread across 3 CALENDAR years (2012 through 2014)

  10. #25
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    How do you intend to count non payments of memberships if you aren't recording a date? Your numbers are based on payment date, if they have no payment date you can't possibly categorize them by month unless you use the ENDDATE to determine the month but then you're really, really doing something you shouldn't in that you're mixing dates (EndDate and PaymentDate).

  11. #26
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by rpeare View Post
    I don't understand what you're asking

    Fiscal year based on payment?
    Fiscal year based on end date?
    What are the limits of your fiscal year?
    Is it the same as the calendar year?

    The query I gave you shows the PAYMENTS by month for every SEASON calendar year for every season past 2014. In the case of the 2017 SEASON (based on end date) you have payments (Based on PAYMENTDATE) spread across 3 CALENDAR years (2012 through 2014)
    Fiscal year based on payment? <--- for the counts per month yes. Counts need to be based on when paid.
    Fiscal year based on end date? <--- ? end date is to provide info on when the membership ends.
    What are the limits of your fiscal year?
    Is it the same as the calendar year? <--- fiscal year is the limit from Jul - Jun

    So if a season ends next year and that membership was paid last year, the count for that membership needs to show in that payment month for that season.
    I'm not sure if I made sense?

    I'm trying to figure out why the 918 in the month of Jun is showing up for the season ending in the future year?

  12. #27
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Quote Originally Posted by rpeare View Post
    How do you intend to count non payments of memberships if you aren't recording a date? Your numbers are based on payment date, if they have no payment date you can't possibly categorize them by month unless you use the ENDDATE to determine the month but then you're really, really doing something you shouldn't in that you're mixing dates (EndDate and PaymentDate).
    Membership record can be created in the Membership table but not contain a Payment so these records need to be counted for non payment of the membership?


    Also, is this correct?
    1. If the [Status] is not equal to 3 (which is Active) it should read "In Active" and the counts for those records in each month.
    Code:
    IIf([Status]=3,"Active","In Active")
    2. This is to show number of Active members each month of the fiscal year (Jul-Jun):
    Code:
     IIf([Status]=3 And [PaymentDate] Is Null,"Active 0 Mbrshp","Active w/ Mbrshp")
    MemberGroup MemberType Expr1 Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Regular Member First Family In Active 8 9 10 12 12 12 13 14



    Regular Member First Family Active 8793 11113 12761 14534 15383 15713 16094 16426 16635


    Regular Member Introductory In Active




    2 2 4 5


    Regular Member Introductory Active 823 1344 1777 2284 2596 2760 3034 3270 3501


    Regular Member Subsequent Family In Active 3 3 3 5 5 6 9 11



    Regular Member Subsequent Family Active 4537 5765 6496 7369 7823 8007 8198 8387 8544



    Blank records though for the Active 0 Mbrshp?

    MemberGroup MemberType Expr1 Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun
    Regular Member First Family Active w/ Mbrshp 8801 11122 12771 14546 15395 15725 16107 16440 16649


    Regular Member First Family Active 0 Mbrshp











    Regular Member Introductory Active w/ Mbrshp 823 1344 1777 2284 2596 2762 3036 3274 3506


    Regular Member Introductory Active 0 Mbrshp











    Regular Member Subsequent Family Active w/ Mbrshp 4540 5768 6499 7374 7828 8013 8207 8398 8555


    Regular Member Subsequent Family Active 0 Mbrshp












    Need to show:
    1. Active which is Status = 3 and has PaymentDate
    2. Active with 0 Memberships which is Status = 3 and there are no payments
    3. Inactive which is Status <> 3

    So the "MemberStatus" field should return:
    Active
    Active w/ 0 Mbrshp
    Inactive

    with the associated counts.


    So I tried this and got nothing.
    Code:
    MemberStatus: IIf([Status]<>3,"Inactive",IIf([Status]=3 And [PaymentDate] Is Null,"Active 0 Mbrshp","Active"))

  13. #28
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ah, I know one part, in the beginning I was doing a count on the Membership ID.

    NoOfMembership was the count of the ID.

    In what you helped to do the running sum, I noticed that it's not counting the ID but the PaymentDate?

  14. #29
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    clear as mud.

    This is the assumption I am making fiscal year 2016 goes from 7/1/2016 through 6/30/2017

    This query shows payments by fiscal year for the 2017 Season:

    Code:
    SELECT lookup_MemberTypes.Description AS MemberType, Format([EndDate],"yyyy") AS SeasonEnding, DatePart("yyyy",[paymentdate])+IIf(DatePart("m",[paymentdate])>=7,1,0) AS PaymentFiscalYear, IIf(Sum(IIf(DatePart("m",[paymentdate])=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])=7,1,0))) AS Jul, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=8,1,0))) AS Aug, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=9,1,0))) AS Sep, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=10,1,0))) AS Oct, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7 And DatePart("m",[paymentdate])<=11,1,0))) AS Nov, IIf(Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=7,1,0))) AS [Dec], IIf(Sum(IIf(DatePart("m",[paymentdate])>=7 Or DatePart("m",[paymentdate])<=1,1,0))=0,Null,Sum(IIf(DatePart("m",[paymentdate])>=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])>=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])>=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])>=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])>=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])>=7 Or DatePart("m",[paymentdate])<=6,1,0))) AS Jun
    FROM lookup_MemberTypes LEFT JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId
    WHERE (((lookup_MemberTypes.Id)=3))
    GROUP BY lookup_MemberTypes.Description, Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate])+IIf(DatePart("m",[paymentdate])>=7,1,0)
    HAVING (((Format([EndDate],"yyyy"))=2017))
    ORDER BY Format([EndDate],"yyyy"), DatePart("yyyy",[paymentdate])+IIf(DatePart("m",[paymentdate])>=7,1,0)
    If that's not what you're after, I don't understand your description. Go back to a simpler example

    24 different people with an enddate of 6/30/2017
    1 Person with a PAYMENTDATE per month from 7/1/2014 through 6/30/2016

    What do you want your end result to look like based on those conditions.

  15. #30
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I am not addressing your second question until this first is worked out, my examples have been based on your original formulas if you want to change what is counted in the columns that's up to you but update your SQL code here so I am not working on outdated information.

Page 2 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-23-2014, 02:06 PM
  2. Rolling Months within a Report
    By RayMilhon in forum Reports
    Replies: 5
    Last Post: 06-14-2013, 03:28 PM
  3. Rolling 90 day lookup Querie
    By Buddus in forum Queries
    Replies: 1
    Last Post: 08-20-2012, 02:09 PM
  4. Rolling Total in Form
    By foxtrot in forum Forms
    Replies: 2
    Last Post: 01-26-2011, 05:45 AM
  5. Rolling 3 Month sums
    By michaeljohnh in forum Reports
    Replies: 1
    Last Post: 11-08-2010, 05:51 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