That worked great - thank you.
>You have to use UNION to comboine 3 reps into one cursor
>
>
>SELECT invpost.salesrep1 as salesrep,
> PADR(ALLTRIM(salesrep.fname) + ' ' + ALLTRIM(salesrep.lname),50) as repname
> FROM invpost
> JOIN salesrep on invpost.salesrep1 = salesrep.salesrep
> WHERE NOT EMPTY(invpost.salesrep1)
>UNION
>SELECT invpost.salesrep2 as salesrep,
> PADR(ALLTRIM(salesrep.fname) + ' ' + ALLTRIM(salesrep.lname),50) as repname
> FROM invpost
> JOIN salesrep on invpost.salesrep2 = salesrep.salesrep
> WHERE NOT EMPTY(invpost.salesrep2)
>UNION
>SELECT invpost.salesrep3 as salesrep,
> PADR(ALLTRIM(salesrep.fname) + ' ' + ALLTRIM(salesrep.lname),50) as repname
> FROM invpost
> JOIN salesrep on invpost.salesrep3 = salesrep.salesrep
> WHERE NOT EMPTY(invpost.salesrep3)
>ORDER BY 1
>INTO CURSOR curRep
>
>
>>Still learning SQL so after a few google searches giving no good results, I figured I would bug the experts. I am trying to get a list of all the unique salesreps (and their names) used in an order into a cursor. The problem is the reps are in three seperate fields on the table: salesrep1, salesrep2, and salesrep3. I am not sure if I need to use three seperate queries and do some sort of combining or if I can do it all in a 1 select statement. The statement below works great for one of the reps. Any help would be appreciated.
>>
>>SELECT distinct invpost.salesrep1 as salesrep,ALLTRIM(salesrep.fname) + ' ' + ALLTRIM(salesrep.lname) as repname FROM invpost,salesrep ;
>> INTO CURSOR curRep WHERE invpost.salesrep1 = salesrep.salesrep AND !EMPTY(invpost.salesrep1) ORDER BY invpost.salesrep1