Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Your first table is almost there. exception of the ClubID ending in 1430 not showing.

    I modified this
    Code:
    SELECT v060ClubOfficersPresOrNot.ClubID, PersonMembership.MembershipTypeId, PersonMembership.EndDate
    FROM v060ClubOfficersPresOrNot LEFT JOIN PersonMembership ON v060ClubOfficersPresOrNot.ClubID = PersonMembership.OrganizationId
    GROUP BY v060ClubOfficersPresOrNot.ClubID, PersonMembership.MembershipTypeId, PersonMembership.EndDate;
    Which shows the Club in question, however it is not showing in Query3, which is the last formula provided.
    And I am not getting the result sought.

    ClubID MembershipTypeId EndDate
    ORG00000201 1 2/5/2015
    ORG00000201 1 6/30/2015
    ORG00000201 1 6/30/2016
    ORG00000201 2 6/30/2015
    ORG00000201 2 6/30/2016
    ORG00000201 4 6/30/2015
    ORG00000380 1 6/30/2015
    ORG00000380 1 6/30/2016
    ORG00000380 2 6/30/2015
    ORG00000380 2 6/30/2016
    ORG00000380 3 6/30/2016
    ORG00000380 3 6/30/2017
    ORG00000380 3 6/30/2018
    ORG00000380 4 6/30/2015
    ORG00000380 4 6/30/2016
    ORG00001430




    ORG00004619 1 3/25/2015
    ORG00004619 1 6/30/2015
    ORG00004619 2 6/30/2015

    ClubOfficerss.zip

  2. #17
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ....
    Ok one last try

    Just remember, I warned you that cartesian queries can be exceptionally cumbersome to execute as you add levels of complexity (instead of just organization you want organization and membership type).

    This is your list of organizations in PersonMembership

    OrganizationId
    ORG00000002
    ORG00000007
    ORG00000082
    ORG00000110
    ORG00000198
    ORG00000201
    ORG00000380
    ORG00004619

    This is your list of organizations in v060ClubOfficersPresOrNot
    ClubID
    ORG00000201
    ORG00000380
    ORG00001430
    ORG00004619

    Your first decision is
    When I am compiling my list of organizations, do I get that list from
    a. PersonMembership
    b. v060ClubOfficersPresOrNot
    c. Both

    Your query qryActiveOrganizations is trying to link both which will not work if you are trying to pull organizations from both tables you will need a union query for that.

    If you want your list of organizations to come from v060ClubOfficersPresorNot that is the only table relevant and qryActiveOrganizations should not include PersonMembership

    In other words in your example database
    Organization 1430 exists in one table but not the other so if you use v060ClubOfficersPresorNot you will ONLY get the organizations present in that table (in this case 201, 380, 1430 and 4619)

    While Organizations 2, 7, 82, 110, 198 exist in PersonMembership but DO NOT exist in v060ClubOfficersPresOrNot so if you use v060ClubOfficersPresOrNot as your source for organization numbers these five organizations will NOT be represented in your final query

    If you want to pick up an organization number REGARDLESS of which table it appears in (in other words you would expect to see these kinds of discrepancies in your actual data) you have to use a union query to find distinct organization numbers.

    I am going to assume you want ONLY organizations that appear in v060ClubOfficersPresOrNot, so your qryActiveOrganizations would be this:

    ClubID
    ORG00000201
    ORG00000380
    ORG00001430
    ORG00004619

    From here you will need a query that has your four membershipID types, I would use the membership TYPE table (you don't have one in your sample database) where you would get something like the above except have 1 through 4 in the rows and something like

    MembershipID
    1
    2
    3
    4

    finally you can leave qrySeasonYears alone

    SeasonYear
    2015
    2016
    2017
    2018
    2019

    When you put these together in a cartesian join you would use this statement:

    Code:
    SELECT qryActiveOrganizations.ClubID, qryMembershipTypes.MembershipID, qrySeasonYears.SeasonYear
    FROM qryActiveOrganizations, qryMembershipTypes, qrySeasonYears;
    You would then link this to your table PersonMembership by *3* fields instead of 2 to perform your counts

    Code:
    SELECT qryActiveOrganizationCartesian.ClubID, qryActiveOrganizationCartesian.MembershipID, qryActiveOrganizationCartesian.TermDate, Count(PersonMembership.OrganizationId) AS TicketsSold
    FROM qryActiveOrganizationCartesian LEFT JOIN PersonMembership ON (qryActiveOrganizationCartesian.TermDate = PersonMembership.EndDate) AND (qryActiveOrganizationCartesian.MembershipID = PersonMembership.MembershipTypeId) AND (qryActiveOrganizationCartesian.ClubID = PersonMembership.OrganizationId)
    GROUP BY qryActiveOrganizationCartesian.ClubID, qryActiveOrganizationCartesian.MembershipID, qryActiveOrganizationCartesian.TermDate;

  3. #18
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Yes, you are correct that the Organization ("Club") info should come from the v060ClubOfficersPresOrNot THEN look at PersonMembership for Member Types and the other Membership info, such as EndDate, etc ...

    Code:
    SELECT qryActiveOrganizations.ClubID, qryMemberTypes.MembershipTypeId, qrySeasonYears.SeasonYear, PersonMembership.EndDate AS TermDate
    FROM qryActiveOrganizations, qryMemberTypes, qrySeasonYears, PersonMembership
    GROUP BY qryActiveOrganizations.ClubID, qryMemberTypes.MembershipTypeId, qrySeasonYears.SeasonYear, PersonMembership.EndDate;
    Results from this doesn't look right.
    Code:
    SELECT qryActiveOrganizationCartesian.ClubID, qryActiveOrganizationCartesian.MembershipTypeID, qryActiveOrganizationCartesian.TermDate, Count(PersonMembership.OrganizationId) AS TicketsSold
    FROM qryActiveOrganizationCartesian LEFT JOIN PersonMembership ON (qryActiveOrganizationCartesian.TermDate = PersonMembership.EndDate) AND (qryActiveOrganizationCartesian.MembershipTypeID = PersonMembership.MembershipTypeId) AND (qryActiveOrganizationCartesian.ClubID = PersonMembership.OrganizationId)
    GROUP BY qryActiveOrganizationCartesian.ClubID, qryActiveOrganizationCartesian.MembershipTypeID, qryActiveOrganizationCartesian.TermDate;
    Changed it to Group on Season and though 15 is correct for 2015, it's repeating 15 for the next few years, which is not correct.
    Also, noticed that it is not limiting it anywhere except in the qrySeason to 6/30 and showing up in this results counts
    ClubID MembershipTypeID SeasonYear TicketsSold
    ORG00004619 1 2015 15
    ORG00004619 1 2016 15
    ORG00004619 1 2017 15
    ORG00004619 1 2018 15
    ORG00004619 1 2019 15
    ORG00004619 2 2015 1
    ORG00004619 2 2016 1
    ORG00004619 2 2017 1
    ORG00004619 2 2018 1
    ORG00004619 2 2019 1
    ORG00004619 3 2015 0
    ORG00004619 3 2016 0
    ORG00004619 3 2017 0
    ORG00004619 3 2018 0
    ORG00004619 3 2019 0
    ORG00004619 4 2015 0
    ORG00004619 4 2016 0
    ORG00004619 4 2017 0
    ORG00004619 4 2018 0
    ORG00004619 4 2019 0

    added
    Code:
     WHERE TermDate >=DateSerial(Year(Date()),6,30)
    and it shows 14 and 1 for 2015, which is correct but it repeats these counts for 2016-2019, which is not correct.ClubOfficerss.zip

  4. #19
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Ok I give up, from my point of view you are altering your requirements every time you post.

    How about this, based on the data in the database you uploaded, you tell me what your expected results are because we are getting nowhere with this conversation.

    Make an excel spreadsheet and fill it out with what you would expect to see exactly based on the data in the sample database.

  5. #20
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Originally posted, need to also see Clubs without memberships but with all of the Club info as shown in the formula I was working with.
    All those fields still need to show.

    These parameters have not changed.
    1. ALL Clubs from the v060ClubOfficersPresOrNot (This is a SQL View that list all relevant info of the Clubs with a President or without AKA: PresOrNot part to identify that View in SQL)
    2. Need these Clubs to also show memberships for the MemberTypeID specified in the current year and future year(s) where then the end-user will enter the EndYear in Crystal Reports.
    3. HOWEVER, need to also see the Clubs, since it needs to show ALL of them in the first place, where there is no membership at all or in future year(s).

    As mentioned ClubID ending in 1430 has no membership info BUT does show however ClubID ending in 4619 only shows when there's membership unlike the 1430. It should show another row of Club info and NULL value for the membership info like it shows in 1430.

    The highlighted row in the worksheet is what needs to show as well for a Club with membership. This is to show that the Club doesn't have a future membership.ClubOfficerss.zip

    When the end-user enters 2015 they would see the grouped results without the highligthed row. So 4619 would show 15 memberships and 1430 would show 0 memberships
    However when the user enters 2016 they would see still see both 4619 and 1430 club info but would say 0 memberships for each.

  6. #21
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Please post what you would expect (excel file would be best) to see based on the data in the sample database you provided.

  7. #22
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    An excel file is in the .zip file.

  8. #23
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I assume the sample result is if you are running a report for organization 5305 only.

    If that is correct what are lines 17 through 21 supposed to show? Lines 1 through 16 correspond to actual records in personmembership but there is no indication for the remaining rows but you have data elements in it that should be coming from v060ClubOfficersPresorNot, there's no end date no nothing to help determine what you intend to show here.

  9. #24
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    No, it's for 5305 and 8004. 8004 is at the 18-21

    Row 17, the highlighted row, is what it also needs to show and not showing.

    So rows 17 - 21 are those that do not show any memberships whereas the first 15 are memberships for 5305.

    As mentioned, need to see ALL Clubs rather they have memberships or not.

    I don't understand why 8004 shows, which is good because 8004 needs to show b/c they don't have memberships.
    But the difference between the two Clubs is that 5305 has memberships (15 of them) in 2015 whereas 8004 has none.

    In the final Grouped and count report it would show
    1. 5305 club info columns then NumberOfMbrshps column showing 15 and the EndYear 2015
    2. 5305 club info columns then NumoberOfMbrshps column showing 0 and the EndYear NULL <--- NOT SHOWING
    3. 8004 club info columns then NumberOfMbrshps column showing 0 and the EndYear NULL <--- IS SHOWING

    Can't figure out why #2 is not showing.

  10. #25
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    We have a basic disconnect here.

    In your sample data you have 1 record in v060ClubOfficersPresOrNot for club 5305
    In your sample data you have 16 records (15 of which with a term of 6/30/2015) in PersonMembership for the same club

    In your sample data you have 4 records in v060ClubOfficersPresOrNot for club 8004
    In your sample data you have 0 records in PersonMembership for the same club

    Because your link between v060ClubOfficersPresOrNot and PersonMembership is the ORGANIZATIONID you should NOT have four records for organization 8004. You should have 1 record. You have four people representing the same organization in v060ClubOfficersPresOrNot, but your stated goal is to show ORGANIZATIONS *not* PEOPLE, therefore 1 record.

    Assuming you agree with that assessment

    How are you figuring you are missing a record? There is no record in your PersonMembership table where an enddate is missing?

  11. #26
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    The 4 "person" records for Club 8004 is correct from v060ClubOfficersPresOrNot IF only wanting to see all Club info and their "officers" info.
    Of which none is the President.

    The v060 is supposed to have data on ALL Clubs and their info WITH President, if a President exist, if not still show the Club info without the President. Which it is.

    ? PersonMembership is only if there's a membership so there should never be a record in that table with a missing EndDate.

    Not all Clubs will have a membership.

    So they want to see ALL Clubs and their President or Not (hence the View name "ClubOfficersPresOrNot" ) and whether they have a membership or not and how many. So would show 15 for 5305 in 2015 0 in 2016, etc ... and 8004 0 in 2015 and 0 in 2016.

    You're an organization ("Club") but haven't purchased a membership or did last time but not this time.
    Report needs to show your Organization with or without membership(s) and the end goal is how many your Club purchased memberships or not at any given time.

    I can do the final grouping and the count once the details of the info sought is figured out.

  12. #27
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    so another way to explain what I've been trying to state, think I'm saying the same thing though ...

    need current and future years AND when there is no membership, including when they had it in the past.
    current year = expired enddate of 6/30/2015. Future year (AKA: "Current" membership) are those with enddate 6/30/2016+4. Because if they have memberships that ended this year on 6/30/2015 are no longer considered "current". The clubs memberships are considered "Current" IF their enddate is in the future. In this case, 6/30/2016 + 4

    Now 8004 doesn't have membership BUT does show and 5305 has a membership BUT does not for next year BUT still needs to show that, even though they have memberships ending this year, 2015, that they don't have a "current" membership which ends in 2016.

    As you've noticed the enddate is 6/30/yyyy. So this year, 2015, are expired, and next year, which is deemed "current" is for 2016 +.

    And ALL Clubs needs to show whether they were current and not current now or didn't have any like 8004.

    So the details show the 15 PersonID with the corresponding Invoices with EndDates for 5305, which will count to having 15 number of memberships in the final grouping and count but for EndYear 2015.
    BUT also needs to show, the grouping step for EndYear 2016 with 0 membership count.
    Like it is for 8004.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-23-2015, 12:14 PM
  2. Replies: 3
    Last Post: 01-21-2014, 12:28 AM
  3. Left OUTER Join to more than one table?
    By oemar00 in forum Queries
    Replies: 3
    Last Post: 09-20-2013, 03:58 PM
  4. Replies: 15
    Last Post: 10-22-2012, 06:06 PM
  5. Outer Join Nested in Inner Join
    By Stevens7 in forum Queries
    Replies: 2
    Last Post: 10-19-2011, 01:34 PM

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