SELECT Query Help - Multiple Records

[Jordan M.]

Hi,

Hoping to get some help modifying the following query that I have...

TABLE: NAMES
ID, FirstName, LastName

TABLE: EMAILS
ID,LinkID,Email,LastUpdateDate

I have two tables, one of Names, one of Emails. Each person only has
one record in the Names table. Each person however, may have multiple
email addresses in the Emails table. I want to pull a query that
returns a person's name, and the latest email address on file.

I am able to successfully pull a full extract that shows names and
emails, but I can't figure out how to limit it to the latest email
address. So in essence, I am getting a row for each name for each
listed email. Which means some names appear multiple times because
they have multiple, older, email addresses on record.

How can I limit this?

THANK!

 

[John Spencer]

You could try a correlated subquery.

SELECT Names.ID, FirstName, LastName, EMail
FROM Names INNER JOIN EMails
ON Names.ID = Emails.LinkID
WHERE EMails.LastUpdateDate =
(SELECT Max(LastUpdateDate)
FROM Emails as E
WHERE E.LinkID = Names.ID)

Or you could use two queries
qLastEmailDate
SELECT LinkID, Max(LastUpdateDate) as Latest
FROM EMails
GROUP By LinkID

Use that query along with the Names table and the email table to get the
information
SELECT Names.ID, FirstName, LastName, EMail
FROM (NAMES INNER JOIN EMails
ON Names.ID = Emails.LinkID)
INNER JOIN qLastEmailDate
ON Emails.LinkID =qLastEmailDate.LinkID
AND Emails.LastUpdateDate = qLastEmailDate.Latest

OR do it all in one query

SELECT Names.ID, FirstName, LastName, EMail
FROM (NAMES INNER JOIN EMails
ON Names.ID = Emails.LinkID)
INNER JOIN (
SELECT LinkID, Max(LastUpdateDate) as Latest
FROM EMails
GROUP By LinkID) as QLastEmailDate
ON Emails.LinkID =qLastEmailDate.LinkID
AND Emails.LastUpdateDate = qLastEmailDate.Latest
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..