I know this sound silly, but I need to try and accomplish this in SQL. I am doing a side project and my manager wants me to create this is Access using only SQL. I have no idea why this choice of tool.

Brenda


>renda,
>
>First create a cursor with the desired structure, ie., Customer_No, >Item_No1, Item_No2, etc. Call it FlatCustomer.
>
>Then
>

>LOCAL lcCustomer_No, lnIndex
>USE FlatCustomer 
>SELECT 0
>USE Customer ORDER Customer_No
>lcCustomer_No = ""
>lnIndex = 0
>SCAN
>
>   DO CASE
>      CASE lcCustomer_No == Customer.Customer_No AND ;
>           BETWEEN(lnIndex,2,9)
>         REPLACE ("FlatCustomer.Item_No" + STR(lnIndex,1)) WITH ;
>            Customer.ItemNo
>      CASE lcCustomer_No == Customer.Customer_No AND ;
>           BETWEEN(lnIndex,10,15)
>         REPLACE ("FlatCustomer.Item_No" + STR(lnIndex,2)) WITH ;
>            Customer.ItemNo
>      CASE lcCustomer_No # Customer.Customer_No 
>         lcCustomer_No = Customer.Customer_No
>         lnIndex = 1
>         INSERT INTO FlatCustomer (Customer_No, Item_No1) ;
>            VALUES (lcCustomer_No, Customer.Item_No)
>   ENDCASE
>   lnIndex = lnIndex + 1
>
>ENDSCAN

>Regards,
Jim