Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Cool Unanswered: SELECT DISTINCT....? Hmmm...

    Can anyone help with the following ACCESS query?

    SELECT DISTINCT [tblUsers].[UserEmail], [tblPSCRComments].[PSCRNumber], [tblUsers].[UserName], [tblPSCRComments].[ID]
    FROM tblPSCRComments INNER JOIN tblUsers ON [tblPSCRComments].[AuthorID]=[tblUsers].[ID]
    WHERE ((([tblPSCRComments].[PSCRNumber])=21004154540))
    ORDER BY [tblPSCRComments].[ID] DESC;

    I need to change it so that rows with the SAME email address are excluded... In other words, if the query returns three rows, and two of them have the same value for the email field, I want one of them to be excluded.

    As you can see, I tried adding the DISTINCT clause... But it still returns rows with duplicate email addresses.

    Any help would be appreciated.

    Thanks

    Mark Wills
    Mark Wills.

  2. #2
    Join Date
    Jan 2003
    Posts
    81
    How about this: (It seems you don't care about which UserName or ID you are diplaying)

    SELECT [tblUsers].[UserEmail], Max([tblPSCRComments].[PSCRNumber]), Max([tblUsers].[UserName]), Max([tblPSCRComments].[ID])
    FROM tblPSCRComments INNER JOIN tblUsers ON [tblPSCRComments].[AuthorID]=[tblUsers].[ID]
    WHERE ((([tblPSCRComments].[PSCRNumber])=21004154540))
    GROUP BY [tblUsers].[UserEmail]
    ORDER BY Max([tblPSCRComments].[ID]) DESC;

    Bjorn

  3. #3
    Join Date
    Mar 2002
    Location
    Shropshire, UK.
    Posts
    25

    Talking Wow!

    Excellent Bjorn! Worked first time! Exactly what I was looking for!

    Thanks VERY much!

    Mark.
    Mark Wills.

Posting Permissions

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