Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410

    OUTER JOIN not producing records when it does not exist in table?


    I have this View and want to also see Clubs that do not have current memberships.

    I have the IS NULL but not seeing the Clubs that do NOT have memberships.
    attribute.PersonMembership is a SQL table that has membership information where the criteria are set for.
    So the

    What is wrong?

    Code:
    SELECT    dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName, 
                          dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code, 
                          RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email, 
                          dbo.v060ClubOfficersPresOrNot.Phone, dbo.v060ClubOfficersPresOrNot.FacilityName, dbo.v060ClubOfficersPresOrNot.StreetOne, 
                          dbo.v060ClubOfficersPresOrNot.StreetTwo, dbo.v060ClubOfficersPresOrNot.City, dbo.v060ClubOfficersPresOrNot.State, dbo.v060ClubOfficersPresOrNot.PostalCode, 
                          YEAR(attribute.PersonMembership.EndDate) AS YearEnd, attribute.PersonMembership.MembershipTypeId, dbo.v060ClubOfficersPresOrNot.URL, 
                          dbo.v060ClubOfficersPresOrNot.Certified, dbo.v060ClubOfficersPresOrNot.FacilityLastUpdate, dbo.v060ClubOfficersPresOrNot.ByLawsUploadDate, 
                          dbo.v060ClubOfficersPresOrNot.ProgramStatusId, dbo.v060ClubOfficersPresOrNot.Status
    FROM         attribute.PersonMembership RIGHT OUTER JOIN
                          dbo.v060ClubOfficersPresOrNot ON attribute.PersonMembership.OrganizationId = dbo.v060ClubOfficersPresOrNot.ClubID
    WHERE     (DAY(attribute.PersonMembership.EndDate) = 30 OR
                          DAY(attribute.PersonMembership.EndDate) IS NULL) AND (MONTH(attribute.PersonMembership.EndDate) = 6 OR
                          MONTH(attribute.PersonMembership.EndDate) IS NULL) AND (YEAR(attribute.PersonMembership.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 1 OR
                          YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId IN (1, 2, 4) OR
                          attribute.PersonMembership.MembershipTypeId IS NULL) OR
                          (DAY(attribute.PersonMembership.EndDate) = 30 OR
                          DAY(attribute.PersonMembership.EndDate) IS NULL) AND (MONTH(attribute.PersonMembership.EndDate) = 6 OR
                          MONTH(attribute.PersonMembership.EndDate) IS NULL) AND (YEAR(attribute.PersonMembership.EndDate) BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 4 OR
                          YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId = 3 OR
                          attribute.PersonMembership.MembershipTypeId IS NULL)
    There's probably a better way to limit the month, day and year range than the way I have it too ...

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    instead of using a null check have you tried checking the length of the trimmed field? I don't know how your SQL table is set up but if you have a text field with blank spaces a null check will not find that as a null value (I'm assuming you're checking the membershiptypeID field here and not the date fields). If membershiptypeID is a NUMBER field (and not a text field) and your enddate field is a date datatype try doing a simpler query first checking to see if 'is null' is correctly picking records for both enddate and mbershiptypeid. It may be that the IS NULL is not the problem but something else is going on.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    The MemberTypeID is a numeric field and the date fields are date datatype.

    The reason for the IS NULL on these fields are because these are the fields in the PersonMembership table and I'm attempting to find records that do not have memberships. I thought that would produce all Club records and club records with memberships but it's not.

    Since there are no other Filters, I'm not sure what it could be. So I thought perhaps it's the JOIN but whatever I change it to it doesn't seem to be resulting in the way it needs to.

    Basically I would expect to see all the fields with data from the Clubs side then NULL on the fields from the PersonMembership side.

    I am going to try in Access.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    What are you trying to do with this query

    Find END DATES that are discrete?

    i.e.
    Find ENDDATE (i.e. 6/30/2015 or 6/30/2016 for membership ID types 1, 2 or 4
    OR Find ENDDATE 6/30/2015, 6/30/2016, 6/30/2017, 6/30/2018 or 6/30/2019 for membership ID type 3,
    OR if the membership ID is null

    Assuming there is no record in attribute.personmembership for a person or organization then checking nulls on both enddate and membershipID is redundant and likely causing this issue.

    You should remove the IS NULL criteria from all your current items and add a THIRD criteria row and have an IS NULL check on the ORGANIZATIONID field (the connecting field).

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Yes on the other Filters.
    MemberTypeID IN (1,2,4) have memberships for a year whereas MemberTypeID = 3 have memberships that last 4 years.
    So want to see all memberships records that exists in the Clubs as well as those clubs without current memberships.

    Is there a better way to filter on the EndDate then the existing?

    Ok, tried this:
    Code:
    WHERE     (DAY(attribute.PersonMembership.EndDate) = 30) AND (MONTH(attribute.PersonMembership.EndDate) = 6) AND (YEAR(attribute.PersonMembership.EndDate) 
                          BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 1 OR
                          YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId IN (1, 2, 4)) AND 
                          (dbo.v060ClubOfficersPresOrNot.ClubNo = 5305) OR
                          (DAY(attribute.PersonMembership.EndDate) = 30) AND (MONTH(attribute.PersonMembership.EndDate) = 6) AND (YEAR(attribute.PersonMembership.EndDate) 
                          BETWEEN YEAR(GETDATE()) AND YEAR(GETDATE()) + 4 OR
                          YEAR(attribute.PersonMembership.EndDate) IS NULL) AND (attribute.PersonMembership.MembershipTypeId = 3) AND 
                          (dbo.v060ClubOfficersPresOrNot.ClubNo = 5305) OR
                          (attribute.PersonMembership.OrganizationId IS NULL) AND (dbo.v060ClubOfficersPresOrNot.ClubNo = 5305)

    Shouldn't it show a row with Club info and NULL in the PersonMembership fields in the results? NOTE: I am getting records that meets this criteria BUT not for who I have information on that should show. I looked at one of the records that it's showing, Club 8004 and they have no memberships at all. So that makes sense for the IS NULL on OrgID.

    It should do both, when there are no memberships at all AND had memberships but don't have any current memberships.

    Club 5305 has memberships in 2015 but not in 2016 and would expect to see it with the IS NULL on OrganizationID, wouldn't it?

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If your table attribute.personmembership contains a HISTORY of membership (not just what the member is currently enrolled with) you would NOT expect to capture it reliably with the 'is null'.

    IS NULL or < 6/30/2015 (where you could fill in the date with a formula based on today's date similar to what you're doing elsewhere.

  7. #7
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Not sure if I"m following ...

    The final report is in Crystal Reports with a EndYear parameter where the end-user enters the membership ending year.
    Right now they are entering to see all of the clubs with current memberships and would like to see the clubs without current memberships.

    So the result basically they are seeing are:

    1. Club 1 has 15 memberships
    2. Club 2 has 10 memberships
    3. Club 3 has 0 memberships or Null memberships <-- trying to get this to show. Knowing that Club 5305 had 15 memberships in 2015 so when the end-user enters 2015 in the prompt they would see Club 5305 with 15 memberships but when they enter 2016 they would still see 5305 but it would either say 0 or null memberships but still listing Club 5305

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you supply a sample dataset, access won't support . in a table name so just change the . to an underscore and give me something to work with so I don't have to recreate stuff.

  9. #9
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Ok, thank you. Appreciate it.

    The "v060ClubOfficersPresOrNot" table consists of the following clubs as the sampling:
    1. ClubNo 5305 - one at issue, has memberships in 2015 but none in 2016 yet not showing up
    2. ClubNo 1617 - has no president but clubs should show and any memberships within the parameters should show
    3. ClubNo 22 - has president and clubs should show with any memberships within the parameters
    4. ClubNo 8004 - has no memberships in the period and does show up

    The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.

    Since the syntax used in Access do not carry over without modifications to SQL, would appreciate the SQL syntax to make it work.
    And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.

    extracting the data set now, will compact, zip then upload.

  10. #10
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Had to redo the extractions. Too much data on the PersonMembership. So I limited to the Clubs listed and randomly selected several others in the mix to reduce the size.

    on the EndYear, need to also limit to 6/30 in prior year, current year and future years.ClubOfficerss.zip

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Here's the basic problem... you are trying to come up with a list for data you are not collecting.

    In other words:

    Company A has 12 tickets purchased for the 2015 season, 3 tickets of each type
    Company A has 0 tickets purchased for the 2016 season (no records in table attribute.personmembership)

    What you want to get when you run the report for 2015 is all the 2015 records, plus a record (1 record I hope) for each year subsequent to 2015 where they have no tickets purchased.

    I have never found a satisfactory way to find non existent records except for running cartesian joins. For example:

    Code:
    SELECT Attribute_PersonMembership.OrganizationIdFROM Attribute_PersonMembership
    WHERE (((Year([EndDate]))=Year(Date())))
    GROUP BY Attribute_PersonMembership.OrganizationId;
    Save this query as qryActiveOrganizations

    Code:
    SELECT Year([EndDate]) AS SeasonYearFROM Attribute_PersonMembership
    WHERE (((Attribute_PersonMembership.EndDate)>=DateSerial(Year(Date()),6,30)))
    GROUP BY Year([EndDate]);
    Save this query as qrySeasonYears

    Code:
    SELECT qryActiveOrganizations.OrganizationId, qrySeasonYears.SeasonYear, DateSerial([seasonyear],6,30) AS TermDate
    FROM qryActiveOrganizations, qrySeasonYears;
    Save this query as qryActiveOrganizationCartesian (note there is no link between the tables, this is by design)

    Code:
    SELECT qryActiveOrganizationCartesian.OrganizationId, qryActiveOrganizationCartesian.TermDate AS EndDate, "NO TICKETS PURCHASED" AS Notice, Null AS MemberShipTypeID, Null AS ClubNo, Null AS SortName, Null AS ClubName, Null AS BSProgram, Null AS ClubSection, Null AS Code, Null AS InvNo, Null AS President, Null AS Email, Null AS Phone, Null AS FacilityName, Null AS StreetOne, Null AS StreetTwo, Null AS City, Null AS State, Null AS PostalCode, Null AS URL, Null AS Certified, Null AS FacilityLastUpdatE, Null AS ByLawsUploadDate, Null AS ProgramStatusID, Null AS Status
    FROM qryActiveOrganizationCartesian LEFT JOIN Attribute_PersonMembership ON (qryActiveOrganizationCartesian.TermDate = Attribute_PersonMembership.EndDate) AND (qryActiveOrganizationCartesian.OrganizationId = Attribute_PersonMembership.OrganizationId)
    GROUP BY qryActiveOrganizationCartesian.OrganizationId, qryActiveOrganizationCartesian.TermDate, "NO TICKETS PURCHASED", Null, Null
    HAVING (((Count(Attribute_PersonMembership.OrganizationId))=0));
    Run this query it will show you 1 record for each organization who had tickets purchased in 2015 who have no tickets purchased through 2019.

    You can ignore all the null fields I was attempting to work this into a union query but I ran into difficulties and didn't want to not respond to this today (I may not have a chance for the remainder of the week).

    The difficulty is you are not creating a record so it's difficult to find out what is missing without some meandering.

    And I'm still not sure what 'level' for lack of a better word of 'missing' ticket sales you're after, is it to the person level or is it just to the organization, the more 'levels' you introduce the harder (and longer the run time) the query becomes.

  12. #12
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    thank you for the info.

    It's at the Club level only. They want to see all clubs and how many memberships each club has in a given year. 2016 right now.
    ORG00004619 doesn't have any memberships in 2016 but did have 15 in 2015.

    Why i thought to say Is Null from the attribute.PersonMembership it would show me all the Clubs in the dbo.v060ClubOfficersPresOrNot and when there are memberships from the attribute.PersonMembership.

    I thought I would see Club 5305 has 15 memberships (invoices) in 2015 (which I am), displaying each invoice and one row that shows the club info but NULL invoice info from the attribute.PersonMembership (which it is not doing).

    I ran the codes and save them as noted.
    I am not seeing Club ORG00001430, who has no memberships. Wouldn't they still show?

    And not sure how I would tie that to the dbo.v060ClubOfficersPresOrNot to get the club officer's info and existing membership counts and still get the results from yours?



    BTW - I don't think SQL allows for DateSerial(). What is the best SQL syntax for use of
    Code:
    >=DateSerial(Year(Date()),6,30)))
    ?

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    In order:
    1.
    It's at the Club level only. They want to see all clubs and how many memberships each club has in a given year. 2016 right now.
    ORG00004619 doesn't have any memberships in 2016 but did have 15 in 2015.


    First of all the query you originally gave has way more information than just a count of tickets. I had assumed you were doing some sort of individual analysis of ticket purchasers. If all you are doing is counting ticket sales by organization by year that's a different (and probably easier) thing to get to.

    Using the same queries I gave you yesterday you just have to create this query as your 'final' step:

    Code:
    SELECT qryActiveOrganizationCartesian.OrganizationId, qryActiveOrganizationCartesian.TermDate AS EndDate, Count(Attribute_PersonMembership.OrganizationId) AS TicketsSold
    FROM qryActiveOrganizationCartesian LEFT JOIN Attribute_PersonMembership ON (qryActiveOrganizationCartesian.TermDate = Attribute_PersonMembership.EndDate) AND (qryActiveOrganizationCartesian.OrganizationId = Attribute_PersonMembership.OrganizationId)
    GROUP BY qryActiveOrganizationCartesian.OrganizationId, qryActiveOrganizationCartesian.TermDate;
    it will give you a count of tickets sold by year. Your original query was breaking down membership types but your most recent post says that doesn't really matter, you are just interested in ticket sales.

    2.
    I thought I would see Club 5305 has 15 memberships (invoices) in 2015 (which I am), displaying each invoice and one row that shows the club info but NULL invoice info from the attribute.PersonMembership (which it is not doing).


    There is no data in your sample for club 5305 so I can't respond to this, I haven't checked everything but my query seems to pulling data correctly.


    3.
    I ran the codes and save them as noted.
    I am not seeing Club ORG00001430, who has no memberships. Wouldn't they still show?


    I based the query qryActiveOrganizations on the table Attribute_PersonMembership. In other words I am looking at any organization that had activity in 2015 as my BASE. If you want to look at ALL of your organizations you would use your table dbo.v060ClubOfficersPresOrNot to come up with your list of clubs. To get organization 1430 to show you'd just have to change the table driving that query then re-run the query I just gave.

    4.
    you can use the CONVERT function in SQL

    http://www.w3schools.com/sql/func_convert.asp

    to change a string to a date

  14. #14
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Apr 2014
    Posts
    410
    Unless I am doing this wrong I am getting the same results as before.

    I am showing 15 invoices for ClubNo 5305 (ClubID: ORG00004619) in 2015 and not the row where it would be null like ClubNo 8004 (ClubID: ORG00001430)

    Which is basically the like the original formula

    Code:
    SELECT PersonMembership.OrganizationId, v060ClubOfficersPresOrNot.ClubNo, v060ClubOfficersPresOrNot.SortName, v060ClubOfficersPresOrNot.ClubName, v060ClubOfficersPresOrNot.Contact, v060ClubOfficersPresOrNot.[USFSA#], v060ClubOfficersPresOrNot.StreetOne, v060ClubOfficersPresOrNot.StreetTwo, v060ClubOfficersPresOrNot.CareOf, v060ClubOfficersPresOrNot.City, v060ClubOfficersPresOrNot.State, v060ClubOfficersPresOrNot.PostalCode, v060ClubOfficersPresOrNot.OrgType, v060ClubOfficersPresOrNot.ClubRegion, v060ClubOfficersPresOrNot.ClubSection, v060ClubOfficersPresOrNot.Email, v060ClubOfficersPresOrNot.Phone, v060ClubOfficersPresOrNot.FirstName, v060ClubOfficersPresOrNot.FacilityName, v060ClubOfficersPresOrNot.PresClubSec, v060ClubOfficersPresOrNot.[PresUSFSA#], v060ClubOfficersPresOrNot.PresCareOf, v060ClubOfficersPresOrNot.PresAdd1, v060ClubOfficersPresOrNot.PresAdd2, v060ClubOfficersPresOrNot.PresCity, v060ClubOfficersPresOrNot.PresState, v060ClubOfficersPresOrNot.PresZip, v060ClubOfficersPresOrNot.President, v060ClubOfficersPresOrNot.PresEmail, v060ClubOfficersPresOrNot.PresPhone, v060ClubOfficersPresOrNot.StatusId, v060ClubOfficersPresOrNot.URL, v060ClubOfficersPresOrNot.Certified, v060ClubOfficersPresOrNot.Code, v060ClubOfficersPresOrNot.FacilityLastUpdate, v060ClubOfficersPresOrNot.ByLawsUploadDate, v060ClubOfficersPresOrNot.BSProgram, v060ClubOfficersPresOrNot.ProgramStatusId, v060ClubOfficersPresOrNot.Status, v060ClubOfficersPresOrNot.IsActive, PersonMembership.MembershipTypeId, PersonMembership.EndDate, PersonMembership.InvoiceNumber, PersonMembership.PaymentDate, PersonMembership.PersonId, Year([EndDate]) AS EndYear
    FROM v060ClubOfficersPresOrNot LEFT JOIN PersonMembership ON v060ClubOfficersPresOrNot.ClubID = PersonMembership.OrganizationId
    GROUP BY PersonMembership.OrganizationId, v060ClubOfficersPresOrNot.ClubNo, v060ClubOfficersPresOrNot.SortName, v060ClubOfficersPresOrNot.ClubName, v060ClubOfficersPresOrNot.Contact, v060ClubOfficersPresOrNot.[USFSA#], v060ClubOfficersPresOrNot.StreetOne, v060ClubOfficersPresOrNot.StreetTwo, v060ClubOfficersPresOrNot.CareOf, v060ClubOfficersPresOrNot.City, v060ClubOfficersPresOrNot.State, v060ClubOfficersPresOrNot.PostalCode, v060ClubOfficersPresOrNot.OrgType, v060ClubOfficersPresOrNot.ClubRegion, v060ClubOfficersPresOrNot.ClubSection, v060ClubOfficersPresOrNot.Email, v060ClubOfficersPresOrNot.Phone, v060ClubOfficersPresOrNot.FirstName, v060ClubOfficersPresOrNot.FacilityName, v060ClubOfficersPresOrNot.PresClubSec, v060ClubOfficersPresOrNot.[PresUSFSA#], v060ClubOfficersPresOrNot.PresCareOf, v060ClubOfficersPresOrNot.PresAdd1, v060ClubOfficersPresOrNot.PresAdd2, v060ClubOfficersPresOrNot.PresCity, v060ClubOfficersPresOrNot.PresState, v060ClubOfficersPresOrNot.PresZip, v060ClubOfficersPresOrNot.President, v060ClubOfficersPresOrNot.PresEmail, v060ClubOfficersPresOrNot.PresPhone, v060ClubOfficersPresOrNot.StatusId, v060ClubOfficersPresOrNot.URL, v060ClubOfficersPresOrNot.Certified, v060ClubOfficersPresOrNot.Code, v060ClubOfficersPresOrNot.FacilityLastUpdate, v060ClubOfficersPresOrNot.ByLawsUploadDate, v060ClubOfficersPresOrNot.BSProgram, v060ClubOfficersPresOrNot.ProgramStatusId, v060ClubOfficersPresOrNot.Status, v060ClubOfficersPresOrNot.IsActive, PersonMembership.MembershipTypeId, PersonMembership.EndDate, PersonMembership.InvoiceNumber, PersonMembership.PaymentDate, PersonMembership.PersonId, Year([EndDate])
    HAVING (((Year([EndDate]))=Year(Date()) Or (Year([EndDate])) Is Null));

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    again.... you are mixing information...

    This query you just posted goes back to producing a bunch of fields which have nothing to do with sales by year by organization, it's a list of individual ticket sales by organization.

    The query I gave you earlier will produce something like this:

    OrganizationId EndDate TicketsSold
    ORG00000002 6/30/2015 39
    ORG00000002 6/30/2016 35
    ORG00000002 6/30/2017 0
    ORG00000002 6/30/2018 0
    ORG00000002 6/30/2019 0
    ORG00000007 6/30/2015 103
    ORG00000007 6/30/2016 99
    ORG00000007 6/30/2017 2
    ORG00000007 6/30/2018 2
    ORG00000007 6/30/2019 0
    ORG00000082 6/30/2015 66
    ORG00000082 6/30/2016 58
    ORG00000082 6/30/2017 0
    ORG00000082 6/30/2018 1
    ORG00000082 6/30/2019 1
    ORG00000110 6/30/2015 5
    ORG00000110 6/30/2016 6
    ORG00000110 6/30/2017 5
    ORG00000110 6/30/2018 3
    ORG00000110 6/30/2019 0
    ORG00000198 6/30/2015 76
    ORG00000198 6/30/2016 62
    ORG00000198 6/30/2017 7
    ORG00000198 6/30/2018 5
    ORG00000198 6/30/2019 1
    ORG00000201 6/30/2015 26
    ORG00000201 6/30/2016 7
    ORG00000201 6/30/2017 0
    ORG00000201 6/30/2018 0
    ORG00000201 6/30/2019 0
    ORG00000380 6/30/2015 40
    ORG00000380 6/30/2016 39
    ORG00000380 6/30/2017 1
    ORG00000380 6/30/2018 2
    ORG00000380 6/30/2019 0
    ORG00004619 6/30/2015 15
    ORG00004619 6/30/2016 0
    ORG00004619 6/30/2017 0
    ORG00004619 6/30/2018 0
    ORG00004619 6/30/2019 0


    Note there is a record for each organization for each year, but it is predicated on the fact the organization MUST HAVE HAD at least 1 ticket sale in the 2015 season. I can not answer questions about data that is not in your database. If you want to include organizations that DO NOT have activity in 2015 but you want to run this query for ALL organizations you would have to change the query qryActiveOrganizations to pull the organization number from the table dbo.v060clubofficerspresornot and NOT attribute.personmembership

    In the data you posted your CLUBIDs are

    In the table dbo.v060ClubOfficersPresOrNot
    ClubID
    ORG00000201
    ORG00000380
    ORG00001430
    ORG00004619

    In the table attribute.PersonMembership
    OrganizationId
    ORG00000002
    ORG00000007
    ORG00000082
    ORG00000110
    ORG00000198
    ORG00000201
    ORG00000380
    ORG00004619


    So organization 1430 does NOT exist in your attribute.personmembership table if you want that organization to appear in the final query modify qryActiveORganizations

Page 1 of 2 12 LastLast
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