Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Images Attached Images
    Carol W.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 .
    Carol W.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •