Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2013
    Posts
    1

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

    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


    CODE

    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;

  2. #2
    Join Date
    Nov 2011
    Posts
    413
    Have you tried setting duplicate values to No?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •