Recently a customer asked me how to determine who has made their final pledge payment within the last 37 days (the choice of 37 was an arbitrary one) AND has no other open pledges?  My first thought was, "Wow!  What a great question!  This fundraiser understands the importance of renewing a donor relationship that is freshly expired."  My next thought, was "Hmmm.....  How do I find them?"

After a few false starts I believe I have the answer.  It has not withstood the test of time, but I'm feeling good about it.

The query text below looks complicated to me too.  The ONLY part you need to focus on is the number 37.  You can change that to any number of days you wish.  If you want to see who made a final pledge payment within the last 60 days, then change the 37 to 60.

You would copy the following text into the SQL tab of your Donor/Voter/Contact/Member list.

 ((EXISTS (SELECT CL044006 FROM TB044002 LEFT OUTER JOIN TB044001 ON CL043998=CL044007 

WHERE ((TB044002.CL044008=TB008485.CL005500) AND

((( ISNULL (( SELECT  SUM ( CL044056 ) FROM  TB044002  WHERE ( CL044008 = TB008485.CL005500 )) ,0 )) 

- ( ISNULL (( SELECT  SUM ( CL045932 ) FROM  TB045928  WHERE ( CL045938 = TB008485.CL005500 )) ,0 )) 

- ( ISNULL (( ISNULL (( SELECT  SUM ( CL048923 ) FROM  TB044002  WHERE ( CL044008 = TB008485.CL005500 )) ,0 )) ,0.0 )))= 0 ))   

and TB044002.cl044170 > getdate () -37 ) 

))

  It looks like this when pasted (click the image for a larger view in your full screen browser):

Your results will vary.  Save the query as a Favorite and you will not need to repeat these steps.