ACCESS FEED: [MS_AccessPros] Re: Select TOP record per group

Kamis, 27 Oktober 2011

[MS_AccessPros] Re: Select TOP record per group

I have it working. I may ask for refinement later. Thanks! Connie

--- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@...> wrote:
>
> 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
>