Show last donation by each Company

[borris]

hi,

i have one table which lists companies and another table that lists the
donations all of the companies have made over the years. these two tables
are joined on the CompanyID field.

i'm trying to build a query in 'query by design' which displays the last
donation each company has made.

i'm selecting 'last' under the date column but when i run the query i'm
basically getting a list of all the donations each company has made.

how do i get the query to show just the last donation by each company?

thanks for your time and assistance.

 

[tina]

try this:

create a new query, and add the CompanyID and donation date fields to the
QBE grid in design view.
next, click the Totals button on the toolbar (looks like a funny capital E).
a Total: line shows in the grid, set to Group By on both fields.
click the Group By line in the date column, it's a combo box.
open the list and change Group By to Max.
go to query datasheet view. you should see each company listed once, with
its' most recent donation date.

hth

 

[borris]

try this:

create a new query, and add the CompanyID and donation date fields to
the QBE grid in design view.
next, click the Totals button on the toolbar (looks like a funny
capital E). a Total: line shows in the grid, set to Group By on both
fields. click the Group By line in the date column, it's a combo box.
open the list and change Group By to Max.
go to query datasheet view. you should see each company listed once,
with its' most recent donation date.

hth

hi, thanks for your reply.

that works if the query only shows the company name (group by) and date
(max).

however if i want it to show the amount donated as well, then i still get
a list of every donation.

any ideas how to solve this?

 

[Gary Walter]

i have one table which lists companies and another table that lists the
donations all of the companies have made over the years. these two tables
are joined on the CompanyID field.

i'm trying to build a query in 'query by design' which displays the last
donation each company has made.

i'm selecting 'last' under the date column but when i run the query i'm
basically getting a list of all the donations each company has made.

how do i get the query to show just the last donation by each company?

Hi Borris,

To briefly sketch what you need to do...

In your query you have now,
put a subquery that finds the max donation
date for a specific CompanyID
in the Criteria row under DonationDate

SELECT
tblCompany.*,
tblDonations.*,
FROM tblCompany
INNER JOIN tblDonations
ON tblCompany.CompanyID = tblDonations.CompanyID
WHERE
tblDonations.DonationDate =
(SELECT Max(t.DonationDate)
FROM tblDonations As t
WHERE t.CompanyID = tblDonations.CompanyID);

That's the best I could do w/o more info from you.

Good luck,

Gary Walter

 

[tina]

no ideas, sorry. better look to Gary for further help now.