Selecting unique record from duplicate

[Damian]

Hi, please help

I've just run a 'find duplicates' query on a table. Now I'm trying to select
a single unique record from each duplicate. One of my fields is called
'OBSERVER' - this contains either a persons name OR 'unknown' if the persons
name is not known. If one of the duplicate records contains a persons name
and the other duplicate is 'unknown', how do I select the record with the
persons name? If both duplicates contain 'unknown' or both contain a name, i
don't mind which record gets selected. See example below...

UniqueID Species Observer
1 A Smith
2 A unknown
3 B unknown
4 B Brown
5 C Jones
6 C Green
7 D unknown
8 D unknown

RESULT

1 A Smith
4 B Brown
5 C Jones
7 D unknown

Thanks in advance, D

 

[Krzysztof Naworyta]

Juzer Damian <[email protected]> napisa³
| Hi, please help
|
| I've just run a 'find duplicates' query on a table. Now I'm trying to
| select a single unique record from each duplicate. One of my fields is
| called 'OBSERVER' - this contains either a persons name OR 'unknown' if
| the persons name is not known. If one of the duplicate records contains
| a persons name and the other duplicate is 'unknown', how do I select
| the record with the persons name? If both duplicates contain 'unknown'
| or both contain a name, i don't mind which record gets selected. See
| example below...
|
| UniqueID Species Observer
| 1 A Smith
| 2 A unknown
| 3 B unknown
| 4 B Brown
| 5 C Jones
| 6 C Green
| 7 D unknown
| 8 D unknown
|
| RESULT
|
| 1 A Smith
| 4 B Brown
| 5 C Jones
| 7 D unknown
|
| Thanks in advance, D


Select
Species,
Nz(Max(iif(Observer='unknown',Null,Observer),'unknown') as Observer2
From qr1
Group by
Species

 

[Damian]

Thank you very much for your help KN, much appreciated
D