Try this:

SELECT areakode, stakode, deviceid, relayminor, relaymajor, ;
deviceid,;
IIF(EMPTY(relaymajor)," ",STR(RECNO('premaint'),4)) as cGroup1, ;
IIF(EMPTY(relayminor)," ",STR(RECNO('premaint'),4)) AS cGroup2 ;
FROM premaint!premaint GROUP BY deviceid,cGroup1,cGroup2 having cnt(*)=1; ORDER BY deviceid

>Greetings Foxperts,
>
>I am trying to do an SQL SELECT based on 3 character fields and 2 memo fields. I can't seem to get the results I'm expecting/wanting.
>
>The source file has these 5 fields (among many others):
>areakode C(2)
>stakode C(3)
>deviceid C(4)
>relayminor Memo
>relaymajor Memo
>
>It's easy to get a table of all deviceid's (distinct), but I'm trying to figure out how to get list of all deviceid's but more than one of a particular deviceid IF there is text in relaymajor OR relayminor.
>
>Here's some code that's not working for me:

>SELECT areakode, stakode, deviceid, relayminor, relaymajor, ;
>	deviceid+IIF(EMPTY(relaymajor),"    ",STR(RECNO('premaint'),4)) ;
>		+IIF(EMPTY(relayminor),"    ",STR(RECNO('premaint'),4)) ;
>			AS cGroup ;
>	FROM premaint!premaint  GROUP BY cGroup  ORDER BY deviceid

>Your comments and advice are appreciated.