How can I lookup all contacts who gave a certain number of gifts within a specific date range?

Troy Wolbrink — Wed, 02 Dec 2009 16:06:06 GMT

Current revision posted to Frequently Asked Questions by Troy Wolbrink on 12/2/2009 4:06:06 PM

How can I lookup all contacts who gave a certain number of gifts within a specific date range?

Filed under: Analysis, Lookups

Custom lookups can be used to lookup about almost anything. To lookup all the contacts who gave a certain number of gifts within a specific date range, you can use a custom lookups.

Here's a custom lookup like this (requires TntMPD 2.1 r10 that will return all contacts who have given 5 gifts or newer)more since 1/1/2009.

In TntMPD, under the "Lookup" menu, click on "Custom". Then copy/paste the following lookup:

SELECT Gift.ContactID
FROM Gift
WHERE (((Gift.[GiftDate])>=#1/1/2009#))
GROUP BY Gift.ContactID
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC

This lookup returns all the contacts who gave 5 gifts or more since 1/1/2009.

When you do this lookup, be sure to check the box "Custom Filter returns a list of Contact IDs". If this box is not available, be sure you're running TntMPD 2.1 r10 or newer.

Click "OK", and the lookup will be sorted with the more frequent givers on top.

By way of another example, here's a custom lookup that returns all contacts who gave between 6 and 12 donations between 1/1/2009 and 12/31/2009:

SELECT Gift.ContactID
FROM Gift
WHERE ((Gift.GiftDate BETWEEN #1/1/2009# AND #12/31/2009#))
GROUP BY Gift.ContactID
HAVING COUNT(*) BETWEEN 6 AND 12
ORDER BY COUNT(*) DESC

How can I lookup all contacts who gave a certain number of gifts within a specific date range?

Troy Wolbrink — Wed, 02 Dec 2009 16:02:02 GMT

Revision 1 posted to Frequently Asked Questions by Troy Wolbrink on 12/2/2009 4:02:02 PM

Custom lookups can be used to lookup about almost anything. To lookup all the contacts who gave a certain number of gifts within a specific date range, you can use a custom lookup like this (requires TntMPD 2.1 r10 or newer):

SELECT Gift.ContactID
FROM Gift
WHERE (((Gift.[GiftDate])>=#1/1/2009#))
GROUP BY Gift.ContactID
HAVING COUNT(*) >= 5
ORDER BY COUNT(*) DESC

This lookup returns all the contacts who gave 5 gifts or more since 1/1/2009.

When you do this lookup, be sure to check the box "Custom Filter returns a list of Contact IDs". If this box is not available, be sure you're running TntMPD 2.1 r10 or newer.

By way of another example, here's a custom lookup that returns all contacts who gave between 6 and 12 donations between 1/1/2009 and 12/31/2009:

SELECT Gift.ContactID
FROM Gift
WHERE ((Gift.GiftDate BETWEEN #1/1/2009# AND #12/31/2009#))
GROUP BY Gift.ContactID
HAVING COUNT(*) BETWEEN 6 AND 12
ORDER BY COUNT(*) DESC