Hi Linda,

I don't know requirements of your application but in general I would recomend to keep data in a normalized form. This way you wouldn't have to change a table structure in the case co_ref4 would be required in the future. Something like

co_id   co_ref
c1      peter
c1      john
c1      mary

The following select will convert your data and remove duplicates

Select co_id, co_ref1 AS co_ref ;
  From mytable ;
  Where co_ref1 <> SPACE(1) ;
  Union ;
  Select co_id, co_ref2 AS co_ref ;
    From mytable ;
    Where co_ref2 <> SPACE(1) ;
  Union ;
  Select co_id, co_ref3 AS co_ref ;
    From mytable ;
    Where co_ref3 <> SPACE(1) ;
  Order By 1,2 ;
  Into Cursor crsTemp1

>Hi all,
>
>I have a table named company. as i forget to set primary key in the table
>i find the table have records like this:

>co_id        co_ref1          co_ref2               co_ref3
>c1            peter            john
>c1                             john                   mary

>Now i want to set the co_id as primary key but the key fields have records
>with same key value. I want to write a sql to combine the above 2 records as:

>c1             peter            john           mary

>I have tried some sql statement but cannot do it. Can someone please tell
>me how? thx.
>
>Linda