Aggregate Query Question – Access 2007

Topic

New Reply

I have a database containing a donor table and a donation table. The common field is donor id. One donor can have many donations.

I have set up an aggregate query to find the latest date for a particular donor. I used the Max function on the date field. This part works perfectly. My query results show the highest date for each donor.

I am also required to show the amount that was given on that latest date. This is where I have a problem. I’m unclear as to what function to use on the totals row for the amount field. See attached screenshot for a picture of the query in question.

Any suggestions or examples of this type of aggregate query would be greatly appreciated.

Reply | Quote

Replies

You have to do this in two steps:

First, create a query based on Donor Table and Donation Table.
Join the tables on Donor ID.
Turn it into a Totals query.
Add the Donor ID field from Donor Table, and set the Total option to Group By (this is the default).
Add the Date Donated field from Donation table, and set the Total option to Max.
Add the Donor Don’t Include field from Donor Table, set its Total option to Where and enter False in the Criteria line.
Do *not* add other fields.
Save this query as – say – qryMaxDate.

Next, create a query based on Donor Table, Donation Table and on the query that you just saved.
Join the two tables on Donor ID
Join Donation Table and the query on Donor ID vs Donor ID and also on Date Donated vs MaxOfDate Donated.
Add the fields from Donor Table and Donation Table that you want to display.
There’s no need to add fields from the query, it’s just there to select the most recent date for each donor.

Reply | Quote

I had figured that it would involve two queries but I was unclear as to how to structure the second one.

Thanks so much, Hans. You’ve come through again .

Reply | Quote