[MS_AccessPros] Select TOP record per group

 

I have two tables that are joined. I would like to only have the record for the top begindate for each Contact show. I can't get it right. Here's what I have currently:

Two tables:
1. LookingEvent with LookingID, BeginDate, etc.
2. LookingContacts with LookingID, ContactID, etc

SELECT LookingEvent.LookingID, LookingContacts.ContactID, LookingEvent.BeginDate, LookingContacts.LookingID
FROM LookingEvent INNER JOIN LookingContacts ON LookingEvent.LookingID = LookingContacts.LookingID
WHERE LookingEvent.LookingID IN
(SELECT TOP 1 LookingContacts.LookingID
FROM LookingEvent As Dupe INNER JOIN LookingContacts AS Dupe2 ON Dupe.LookingID = Dupe2.LookingID
ORDER BY LookingContacts.ContactID, LookingEvent.BeginDate DESC);

Thanks!
Connie

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (1)

Recent Activity:

• New Members 9
• New Files 3

Visit Your Group

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___