Unique ID and Latest Date

[KerryF]

My tblPeople table has a unique ID, people's names, and address. The
tblContrib table has a unique ID for each contribution, the date of the
contribution (which is unique for a given date and person's ID), the ID from
the tblPeople table, and other fields. Being that one person can make many
contribution over time the tblPeople ID will appear many times in the
tblContrib table. If a person has never made a contribution, then their ID
will not be in the tblContrib table.

From the tblContrib table, I want a list of unique tblPeople table ID's and
the cooresponding date of the contribution. When a person has given multiple
contributions over the years, the date of the contribution must be the latest
date. If a person has given only one contribution several years ago, then I
want that ID and date.

Thanks for your help.

 

[Jeff Boyce]

Kerry

If this were mine, I'd create a new query in design view, add in the
tblContrib table, and use a Totals query to GroupBy the PersonID and use the
Max() aggregation function on the ContributionDate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KerryF]

I need the list of people who have made contributions. If a person has made
more than one contribution, then only the one with the latest date is to be
listed.

 

[John Spencer]

If all you need is the date of the last contribution then

SELECT tblPeople.FirstName
, LastName
, Address
, Max(ContributionDate) as LastGiven
FROM tblPeople LEFT JOIN tblContrib
ON tblPeople.UniqueID = tblContrib.UniqueID
GROUP BY FirstName, LastName, Address

In query design view (the grid)
-- Add both tables
-- Join tblPeople to tblContrib by dragging from UniqueId to UniqueID
-- If you want to show everyone in tblPeople even if they have not contributed
then double-click on the join line and change it to show all in tblPeople and
only matching in tblContrib
-- Add all the fields you want to see in tblPeople.
-- Add ONLY the ContributionDate field from tblContrib
-- Select View: Totals from the menu
-- Change GROUP By to MAX under the ContributionDate field
-- Run the query


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

 

[Jeff Boyce]

What happens when you use the Totals query?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KerryF]

Jeff,

The Totals query worked great! At first I did not understand what this query
was doing. I'm new to MS Access.

Thanks very much for your help.