Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DISTINCT statement (2003)

    I know I should know this but I am drawing a big blank.

    I want to create a query that pulls a number of fields from a table. But I want a certain field (the EncounterNumber) to be distinct.

    What is SQL statement for doing this? Or the way for doing this in Access. Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DISTINCT statement (2003)

    What do you want to happen with the other fields in the query if they are not distinct for a specific EncounterNumber? You'll have to decide whether to return the sum (for numeric fields), or the minimum value, or the maximum value, or ...

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DISTINCT statement (2003)

    What I really want is to display all the fields for the record. But I just want to see the records where the EncounterNumber is distinct. Is that possible? Thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DISTINCT statement (2003)

    If I understand you correctly, in the following situation

    <table border=1><td>EncounterNumber</td><td>OtherFields</td><td align=right>1</td><td>a</td><td align=right>1</td><td>b</td><td align=right>2</td><td>c</td><td align=right>3</td><td>d</td><td align=right>3</td><td>e</td><td align=right>3</td><td>f</td><td align=right>4</td><td>g</td><td align=right>4</td><td>h</td><td align=right>5</td><td>i</td></table>
    you'd want to return the records with EncounterNumber 2 and 5, because they only occur once. This is the complement of a Find Duplicates query:
    - Create a query based on your table (let's call it tblData).
    - Add the fields you need, including EncounterNumber.
    - Set the criteria for EncounterNumber to

    In (SELECT [EncounterNumber] FROM [tblData] As Tmp GROUP BY [EncounterNumber] HAVING Count(*)=1)

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DISTINCT statement (2003)

    Thanks, that will do the trick

Posting Permissions

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