DISTINCT statement (2003)

[hohepa]

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

[HansV]

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 ...

[hohepa]

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

[HansV]

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)

[hohepa]

Thanks, that will do the trick