There are two relationship types to consider when creating a query: one-to-one and one-to-many.
- An example of a one-to-one relationship is the Last Name field on a constituent record. There is one last name to one constituent.
- An example of a one-to-many relationship is a donor's giving history. There are several gift records to one constituent.
The First Gift, Last Gift, and Largest Gift query categories are one-to-one relationships. For any constituent who has donated, there is one first gift, one largest gift, and one last or latest gift he has given.
A common mistake is to try to find the largest gift a donor gave in a specific year by creating a query with the filters Largest Gift Amount greater than or equal to $1 and Largest Gift Date between 1/1/2000 and 12/31/2000. You expect the query will find the largest gift given in 2000. However, the query will actually find constituents whose largest gift is greater than or equal to $1 and is in 2000. For example, Michael Simpson gives $500 in 1999 and $100 in 2000. He would not be included in the query because he gave his largest gift in 1999, not 2000. It becomes a question of semantics. The query is not asking for the largest gift a donor gave in 2000, but whether the largest gift a donor ever gave is in 2000.
This same logic applies to First Gift and Last Gift. When you use First Gift Date between 1/1/2000 and 12/31/2000, you are asking for constituents who became first-time donors in 2000. When you use Last Gift Date between 1/1/2000 and 12/31/2000, you are asking for constituents who last gave in 2000.
If you need the first, last, or largest amount given within a specific time period, use the First Greatest Latest Report. This analytical report identifies the first gift, last gift, and largest gift given within a specified time period. Additional filters include Gift Type, Campaign, Fund, and Appeal. You could run this report to identify a donor's largest stock gift to a specific campaign. The report operates differently from Query. Refer to our example where Michael Simpson gave $500 in 1999 and $100 in 2000. If you run the First Greatest Latest Report for 2000, Michael shows with $100. In addition to using the report to display the constituents who meet the criteria, you can create an output query of these constituents to use elsewhere in the program.
Query and Reports are very different tools with specific purposes. However, the differences are not as subtle as they appear. Choose the right tool for the task.