Records Based on Recent Date

[William Wisnieski]

Hello Everyone,

I'm helping out a non-profit school with their database. They would like to
know the last gift made by each donor, the donor name, and the gift amount.

I built a query based on two tables. The donor name is from the first table
[Gen_info]. The gift [Date] and [Gift] are in the second table [Gift
Detail].

The query works except for one problem. It will return the proper number of
records with the most recent gift date as long as I don't add the [gift]
field (which is the dollar amount). As soon as I add the [gift] field it
gives me all the gifts for each donor instead of the most recent. The gift
amount is a critical piece the administrators want to see.

Here is the code I have that works:

SELECT Gen_info.ID, Gen_info.FirstName, Gen_info.LastName, Max([Gift
Detail].Date) AS MaxOfDate
FROM Gen_info INNER JOIN [Gift Detail] ON Gen_info.ID = [Gift Detail].ID
GROUP BY Gen_info.ID, Gen_info.FirstName, Gen_info.LastName;

Here is the code that does not work when I add the [gift] field.

SELECT Gen_info.ID, Gen_info.FirstName, Gen_info.LastName, [Gift
Detail].Gift, Max([Gift Detail].Date) AS MaxOfDate
FROM Gen_info INNER JOIN [Gift Detail] ON Gen_info.ID = [Gift Detail].ID
GROUP BY Gen_info.ID, Gen_info.FirstName, Gen_info.LastName, [Gift
Detail].Gift;

Thanks for your help,

William

 

[John Vinson]

The query works except for one problem. It will return the proper number of
records with the most recent gift date as long as I don't add the [gift]
field (which is the dollar amount). As soon as I add the [gift] field it
gives me all the gifts for each donor instead of the most recent. The gift
amount is a critical piece the administrators want to see.

Rather than a Totals query, use a Subquery to select the most recent
gift:

SELECT Gen_info.ID, Gen_info.FirstName, Gen_info.LastName, [Gift
Detail].[Date]
FROM Gen_info INNER JOIN [Gift Detail] ON Gen_info.ID = [Gift
Detail].ID
WHERE [Gift Detail].[Date] =
(SELECT Max([X].[Date] FROM [Gift Detail] AS X
WHERE X.ID = [Gen_Info].ID)
ORDER BY LastName, FirstName;

John W. Vinson[MVP]

 

[William Wisnieski]

Thanks John. That worked! I'm curious about something though. For the
heck of it I substituted the following:

(SELECT Max([Date]) FROM [Gift Detail] WHERE ID = [Gen_Info].ID)

in place of

(SELECT Max([X].[Date] FROM [Gift Detail] AS X WHERE X.ID = [Gen_Info].ID)

And I got the same exact results. Do you know why?

Thanks Again,

William

The query works except for one problem. It will return the proper number of
records with the most recent gift date as long as I don't add the [gift]
field (which is the dollar amount). As soon as I add the [gift] field it
gives me all the gifts for each donor instead of the most recent. The gift
amount is a critical piece the administrators want to see.

Rather than a Totals query, use a Subquery to select the most recent
gift:

SELECT Gen_info.ID, Gen_info.FirstName, Gen_info.LastName, [Gift
Detail].[Date]
FROM Gen_info INNER JOIN [Gift Detail] ON Gen_info.ID = [Gift
Detail].ID
WHERE [Gift Detail].[Date] =
(SELECT Max([X].[Date] FROM [Gift Detail] AS X
WHERE X.ID = [Gen_Info].ID)
ORDER BY LastName, FirstName;

John W. Vinson[MVP]

 

[John Vinson]

Thanks John. That worked! I'm curious about something though. For the
heck of it I substituted the following:

(SELECT Max([Date]) FROM [Gift Detail] WHERE ID = [Gen_Info].ID)

in place of

(SELECT Max([X].[Date] FROM [Gift Detail] AS X WHERE X.ID = [Gen_Info].ID)

And I got the same exact results. Do you know why?

I just got carried away. You need the alias when you're linking the ID
in the subquery to an ID in the *same table* in the outer query; in
this case you have the option of using a different table, so there's
no need for the alias! Good move!

John W. Vinson[MVP]