>
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,
>JimBrenda,
I think that's stretching the envelope a little. Can you do it as an SQL query to get the data into VFP, then process it as above and do an SQL Update back into Access?
Wish you luck,
Jim
