SELECT DISTINCT ID is returning Multiple ID's HELP!!

[fuonge]

I am trying to get a unique/distint return of ID's but it keeps returning with multiple ID's I keep trying to look at the SQL but it doesn't make sense to me. I realize there are two[RTR_Enrollment History].[Date of Update] for 1 ID and I want to select the MAX or most recent [RTR_Enrollment History].[Date of Update].

Shows


ID DATE_OF_UPDATE NAME
1 10/1/2011 A
1 7/1/2011 A
2 7/1/2011 B
3 7/1/2011 C
4 7/1/2011 D
4 7/8/2011 D

WANT
ID DATE_OF_UPDATE NAME
1 10/1/2011 A
2 7/1/2011 B
3 7/1/2011 C
4 7/8/2011 D

SELECT DISTINCT [RTR_Basic Information].ID, [RTR_Enrollment History].[Date of Update], [RTR_Basic Information].[First Name], [RTR_Basic Information].[Last Name], [RTR_Basic Information].[Date Added], RTR_Demographics.[Date of Birth], [RTR_Basic Information].CurrentAgeM, [Look Up: Progam].ProgramLabel, RTR_Demographics.Consented, [RTR_Data Collection History].SurveyDate, [RTR_Data Collection History].[Payment Made], [RTR_Data Collection History].Video, [RTR_Data Collection History].[LENA sent home], [RTR_Data Collection History].[LENA rec'd], [RTR_Data Collection History].[Notes-Data Collection]

FROM ([RTR_Basic Information] INNER JOIN ([RTR_Data Collection History] INNER JOIN RTR_Demographics ON [RTR_Data Collection History].ID = RTR_Demographics.ID) ON ([RTR_Basic Information].ID = RTR_Demographics.ID) AND ([RTR_Basic Information].ID = [RTR_Data Collection History].ID)) INNER JOIN ([RTR_Enrollment History] LEFT JOIN [Look Up: Progam] ON [RTR_Enrollment History].Program = [Look Up: Progam].ProgramV) ON [RTR_Basic Information].ID = [RTR_Enrollment History].ID

WHERE ((([Look Up: Progam].ProgramV)=8 Or ([Look Up: Progam].ProgramV)=9 Or ([Look Up: Progam].ProgramV)=10) AND (([RTR_Basic Information].Status)=1 Or ([RTR_Basic Information].Status)=2 Or ([RTR_Basic Information].Status) Is Null) AND (([RTR_Data Collection History].Done)=2 Or ([RTR_Data Collection History].Done) Is Null))
ORDER BY [RTR_Basic Information].ID;

[June7]

Each record is distinct because the name changes.

Explore TOP n parameter. Review: http://allenbrowne.com/subquery-01.html#TopN

[fuonge]

Thank you for your response. With my code listed, How does the TOP fit in with all the FROM inner and left join.

Would I just add a subquery underneath the WHERE as listed and how would I have to Identify the similar inner and left joins or Can I simply add a subquery that looks like:

(SELECT TOP [RTR_Enrollment History].[Date of Update]
FROM [RTR_Enrollment History])

[fuonge]

Here is my attempt.. but I think I sort of made it worse. I keep getting an error


SELECT DISTINCT [RTR_Enrollment History].[Date of Update], [RTR_Basic Information].ID, [RTR_Basic Information].[First Name], [RTR_Basic Information].[Last Name], [RTR_Basic Information].[Date Added], RTR_Demographics.[Date of Birth], [RTR_Basic Information].CurrentAgeM, [Look Up: Progam].ProgramLabel, RTR_Demographics.Consented, [RTR_Data Collection History].SurveyDate, [RTR_Data Collection History].[Payment Made], [RTR_Data Collection History].Video, [RTR_Data Collection History].[LENA sent home], [RTR_Data Collection History].[LENA rec'd], [RTR_Data Collection History].[Notes-Data Collection]


FROM ([RTR_Basic Information] INNER JOIN ([RTR_Data Collection History] INNER JOIN RTR_Demographics ON [RTR_Data Collection History].ID = RTR_Demographics.ID) ON ([RTR_Basic Information].ID = RTR_Demographics.ID) AND ([RTR_Basic Information].ID = [RTR_Data Collection History].ID)) INNER JOIN ([RTR_Enrollment History] LEFT JOIN [Look Up: Progam] ON [RTR_Enrollment History].Program = [Look Up: Progam].ProgramV) ON [RTR_Basic Information].ID = [RTR_Enrollment History].ID

WHERE

(Select Top [RTR_Enrollment History].[Date of Update] AND (([Look Up: Progam].ProgramV)=8 Or ([Look Up: Progam].ProgramV)=9 Or ([Look Up: Progam].ProgramV)=10) AND (([RTR_Basic Information].Status)=1 Or ([RTR_Basic Information].Status)=2 Or ([RTR_Basic Information].Status) Is Null) AND (([RTR_Data Collection History].Done)=2 Or ([RTR_Data Collection History].Done) Is Null))

ORDER BY [RTR_Basic Information].ID;

[June7]

Need a unique ID field in the table. An autonumber type will serve. I called it RID.

With the sample data from first post, this works:

SELECT * FROM Table1 WHERE RID IN (SELECT TOP 1 RID FROM Table1 As Dupe WHERE
Dupe.ID=Table1.ID ORDER BY Dupe.ID, Dupe.Date_Of_Update DESC);


BTW, Name is a reserved word. Should not use reserved words as names.