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

[Johnny Meredith]

Actually, the query is working properly. Since each gift amount is
different, it displays all gifts because it can't group differing
amounts. Try removing [Gift Detail].Gift from the group by clause and
put it in the select clause. Post-back if that doesn't work, and I'll
try it along with you in Access.

[William Wisnieski]

Thanks Johnny,

I tried your suggestion and got the following error message:

"You tried to execute a query that does not include the specified expression
'Gift' as part of an aggregate function."

This is the code I used which resulted in that error message:

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;

William
"Johnny Meredith" <jm*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Actually, the query is working properly. Since each gift amount is
different, it displays all gifts because it can't group differing
amounts. Try removing [Gift Detail].Gift from the group by clause and
put it in the select clause. Post-back if that doesn't work, and I'll
try it along with you in Access.

[William Wisnieski]

Well, with help a Microsoft MVP, I was able to get the query right. I ended
up using a Subquery instead of a Totals query. Here's the code that works:

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

Thanks,

William
"William Wisnieski" <wi***************@verizon.net> wrote in message
news:HbFle.1672$zb.57@trndny02...

Thanks Johnny,

I tried your suggestion and got the following error message:

"You tried to execute a query that does not include the specified expression 'Gift' as part of an aggregate function."

This is the code I used which resulted in that error message:

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;

William
"Johnny Meredith" <jm*******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Actually, the query is working properly. Since each gift amount is
different, it displays all gifts because it can't group differing
amounts. Try removing [Gift Detail].Gift from the group by clause and
put it in the select clause. Post-back if that doesn't work, and I'll
try it along with you in Access.