Access: First/Last & Min/Max

[DKTaber]

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?

 

[THoey]

For this you are going to want to use the MAX function on your date field. What I would ask is that you change the view to the SQL view, and then cut and paste your code here. It would be easier for us and you to give you a quick solution.

 

[DKTaber]

Terry: Thanks for the quick response. Just before getting your message, I did write a query using "Max" on the contribution date, and it does correctly output the latest date where the membership type is "M". That's fine as far as it goes, but on the report for which the query is the data source, I also need to show the membership type (actual field name is just [Type]). As you see from the SQL code below, [Type] is only in the query as a "Where". If I put [Type] in there again so I can use the field in the report, it outputs EVERY record for the member that begins with "M". What I want, of course, it ONLY the [Type] associated with the record having the Max Date. How can I do that?

SELECT MemVol.Lastname, MemVol.Firstname, Max(Contributions.Date) AS MaxOfDate
FROM MemVol INNER JOIN Contributions ON MemVol.ID = Contributions.ID
WHERE (((Contributions.Type) Like "M*" And (Contributions.Type)<>"MAD") AND ((Month([DateJoined]))=[Enter month]) AND ((Year([Date]))<Year(Date())))
GROUP BY MemVol.Lastname, MemVol.Firstname
ORDER BY MemVol.Lastname, MemVol.Firstname, Max(Contributions.Date) DESC;

 

[downwitchyobadself]

Hello again. This is a bit tricky, because you're trying to do two things at once: 1, figure out when the last member contribution was made, and 2, figure out what type of contribution it was.

These require two different kinds of queries: the group-by, which you've correctly set up, returns the date as you want. But you need a separate query, based on that group-by query, which returns the type of contributions

As long as there can never be more than one type of contribution on a given day, you're okay. Here's what you do:

1. Build the group-by query as you have, I'll pretend it's called qryMaxContDte, then create a new query.
2. To the new query in design view, add qryMaxContDte and the Contributions table.
3. Link the MaxOfDate field in qryMaxContDte to the Contributions.Date field.
4. Add the Contributions.Type field to the grid, along with whatever other fields you need in the report.

As I say, this only works if you have one contribution per type per day. Otherwise, you may still get multiple records. The real solution to this is to search not on a date, but on a key field: If you had an AutoNumber ContributionID field, for example (and you may still add one to your table, of course), and returned the Max value of that ContributionID where the type was as you wished, you could then link in your second query between the MaxOfContributionID field and key in the Contributions table, and you'd be guaranteed only one exact record. But that may be irrelevant here; you'll know soon enough.

By the way, "Date" and "Type" and such are really bad ideas for field names. Read about that here.

 

[DKTaber]

Dr. Down: I will try as you have prescribed and will let you know how I make out.

Yes, I realize "Date" and "Type" are not good ideas for field names. I designed these long ago...before I grew up with Access...and (hate to admit it) I've been too lazy to change them because of the work required to change all the queries and forms. I would have already done that, of course, if I had encountered problems based on the field names...but I haven't.

 

[DKTaber]

Dr. Down: It appears to work. I did some "finagling" to get the data I wanted (including, to my surprise, I had to instruct it to give me only Type's beginning with "M"...again, setting the properties to unique records), and adding another table contain all the basic member data, but what it produced appears to be right on the mark. I only used two records as a test for this (in which I entered multiple membership dates and types), but for those two, it produces the right data...which no other queries have.

Here's the SQL statement I wound up with:

SELECT DISTINCT MemVol.Lastname, MemVol.Firstname, Contributions.Type, RenewalMaxDate.MaxOfDate
FROM MemVol INNER JOIN (RenewalMaxDate INNER JOIN Contributions ON RenewalMaxDate.MaxOfDate = Contributions.Date) ON MemVol.ID = Contributions.ID
WHERE (((Contributions.Type) Like "M*" And (Contributions.Type)<>"MAD"))
ORDER BY MemVol.Lastname, MemVol.Firstname;

I think the problem is solved. Many thanks for the help.