Need help: Excluding records from found set based on multiple fields/criteria
Hello and thanks for viewing!
I would first like to apologize: This is my first time posting and I am sure there is a previous post that tackled this subject, but I was unable to find anything helpful using the forum search (probably using diffrent terminology than I should).
I have a solution that I'm developing for membership organizaiton. My "memberships" table has a many to one relationship with my "individuals" and "groups" tables, respectively. The fellow who set up the solution originally had a unique record in the "memberships" table for each membership (group or individual) and these are all subject to expiration. At the time of membership renewal, a new, unique record is created for the membership.
The problem lies in that when searching for "expired" members (we do membership drives for a large part of our fund raising), the expired memberships records appear in the found set regardless of the group or individual's current, un-expired membership record. I need to exclude any "expired" records that match (whatever field, I have a foreign key set up for both Individuals and Groups, or even just by name) any "current" records. This helps us to avoid sending membership drive letters and emails to folks who have already renewed, thus saving us a ton of money and headaches from unhappy members communicating their displeasure at receiving inappropriate solicitation.
Whew! Hope that is clear enough, and I would very much appreciate any suggestions! I am hoping to resolve this with a "magic button" that triggers a script that does the find and exclusion, but I am NOT yet an expert at writing scripts. Would like to avoid messing with relationships and the solution infrastructure as much as possible. THANKS!