Incorrect results when grouping in a query

[Tammy]

Hello,

I am trying to group data in a query (Access 2007). The scenario is this: I
have a database that keeps track of monetary gifts. Part of the query is
coming out correct - I group by the donor's ID and a few other fields, then,
I want to pull the most recent date of donation and display the amount for
that donation. I've read through quite a few post, but am not familiar with
programming and am hoping there is a way to accomplish this through Design
view.

For example, I have ID #17 who has given (6) donations over the past few
years. What I'd like to do is display only the most recent donation.
10/13/2006, donated $100
11/15/2005, donated $200

Only show me 10/13/2006 with the $100.

I am doing this for multiple IDs, not just on one ID at a time.

It seems pretty straight forward to me until i run the results. I grouped
all ID's, then figured out the MAX date to come up with the most recent date
- getting to the correct donation is the problem. The grouping works for the
ID's, the results display the most recent date, but the amount donated does
not match the date. I just cannot figure out how to get the grouped ID's to
display the most recent donation date along with the amount donated on that
date - I've tried all the choices in the Totals row for the amount field
hoping to stumble upon the one I need.

I'm sorry, I am not familiar with SQL, so any suggestions in plain 'ole
English will be appreciated very much! Please let me know if more information
is needed.

Thanks!!

 

[John Spencer]

Two query result.

Create a query that groups by donor id and returns the maximum date
Save that query

Now open a new query on the donations table and add the saved query to the it.
Join the table to the query on the donor id and the date fields
Select the fields from the table you want to display.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Tammy]

John! Cannot thank you enough! I did try creating a grouped query and basing
my results query on that one, but did not link on the date, as well as the
ID. Your solution worked great!

Thank you again!!

 

[dltoney]

How would this solution work with 3 tables?