>Hello,
> I have a table which contains the following fields:
> order_id
> rep1_id
> rep2_id
> rep3_id
> com1_pct
> com2_pct
> com3_pct
>How can I use a sql statement to reorganize the table which contains:
> order_id
> sale_id
> commission
>so if rep1, rep2,rep3 are unique, the record will become 3 records and teh commission will be com1_pct, com2_pct and com3_pct, if rep1,rep2 and rep3 are same, the record will be still one record but commission will be the sum of the com1_pct, com2_pct and com3_pct.

Break up the problem, and try:

SELECT 0
CREATE CURSOR SepRecs (OrderId C(<i>whatever</i>), Sale_ID C(<i>whatever</i>), comm_pct N(7,4))
SELECT 0
USE DenormalTable
SCAN
   IF ! EMPTY(rep1_id)
      INSERT INTO SepRecs VALUES (DenormalTable.Order_ID, DenormalTable.rep1_ID, com1_pct)
   ENDIF
   IF ! EMPTY(rep2_id)
      INSERT INTO SepRecs VALUES (DenormalTable.Order_ID, DenormalTable.rep2_ID, com2_pct)
   ENDIF
   IF ! EMPTY(rep3_id)
      INSERT INTO SepRecs VALUES (DenormalTable.Order_ID, DenormalTable.rep3_ID, com3_pct)
   ENDIF
ENDSCAN
SELECT 0
SELECT OrderID, Sale_ID, SUM(comm_pct) as commision ;
  FROM SepRecs ;
  INTO CURSOR SummaryResults ;
  GROUP BY OrderID, Sale_ID
USE IN SepRecs

I assume you're aware that you're computing a percentage for commission, you don't have a sales amount

>thanks
>sherry