I have not often had to use the total row in queries where you "Group By" fields, or use Sum, Last, First, Min, Max, etc. Through experimentation, one can figure out what most of the choices do, but I'm having a horrendous time trying to use Last or First.
I had thought that Last, for example, would pick out the last record SUBJECT TO OTHER CRITERIA IN THE QUERY. That is, if the query sorts by descending date, the Last record would be the earliest date; the First record would be the most recent date.
The specific problem involves a member table linked to a contributions table, one-to-many (i.e., single member can make many contributions during his/her membership). There are two major categories of "contributions": Membership dues, and outright gifts. The membership dues have several levels from "Individual" ($35) through "Lifetime" ($2,500). Each membership level has an identifier in a [memtype] field, and ALL start with "M"; e.g., "MIN" for "Individual", "MFA" for "Family", etc. All outright gifts start with "C".
Each month we want to send letters to all but Lifetime members asking them to renew. The query selects those members whose join date (several years ago) was that month. But it then must look ONLY at contribution types beginning with "M". Of course, there will be many "M*s" under most members' records, and we only want the query to select the most recent renewal, which will in most cases -- but not all -- be last year.
How can I do that?
I had thought that Last, for example, would pick out the last record SUBJECT TO OTHER CRITERIA IN THE QUERY. That is, if the query sorts by descending date, the Last record would be the earliest date; the First record would be the most recent date.
The specific problem involves a member table linked to a contributions table, one-to-many (i.e., single member can make many contributions during his/her membership). There are two major categories of "contributions": Membership dues, and outright gifts. The membership dues have several levels from "Individual" ($35) through "Lifetime" ($2,500). Each membership level has an identifier in a [memtype] field, and ALL start with "M"; e.g., "MIN" for "Individual", "MFA" for "Family", etc. All outright gifts start with "C".
Each month we want to send letters to all but Lifetime members asking them to renew. The query selects those members whose join date (several years ago) was that month. But it then must look ONLY at contribution types beginning with "M". Of course, there will be many "M*s" under most members' records, and we only want the query to select the most recent renewal, which will in most cases -- but not all -- be last year.
How can I do that?