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