Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Cetin Basoz
  Where is Cetin Basoz?
 Izmir
 Turkey
 Cetin Basoz
 To: Philip Lawson
  Where is Philip Lawson?
 
 Ghana
 Philip Lawson
 Tags
Subject: RE: changing each record to one to many
Thread ID: 143478 Message ID: 143521 # Views: 1 # Ratings: 0
Version: Not Applicable Category: Databases, Tables and SQL Server
Date: Wednesday, August 29, 2007 2:00:58 PM         
   


> I have a datafile which contains records on each person catalogued in the fields below :
>
>
> Fields
>
> 1. Idno
> 2. Fname
> 3. Lname
> 4. num_child
> 5. chld1_name
> 6. sex1
> 7. date1_bth
> 8. chld2_name
> 9. sex2
> 10. date2_bth
> 11. chld3_name
> 12. sex3
> 13. date3_bth

> etc , etc
>
>
>
> For each master record I'd want to have a program which will generate the corresponding number of child records as follows:
>
> Fields
> 1. Idno
> 2. Fname
> 3. Lname
> 4. Chld_name
> 5. Sex
> 6. date_bth

>
> This would enable me to have a number of child record(s) for each employee instead of the existing structure.
>
> The connection is spt client server
>
>
>
> Thanks

SQLExec(m.lnHandle, "select * from mySPTTable", "myCursor")
SQLDisconnect(m.lnHandle)

local ix,chld_name,sex,date_bth

select Idno,Fname,Lname from myCursor into table parentNames
create table children (idno i, chld_name c(30), Sex i, date_bth d)
select myCursor
scan
 idno = myCursor.idNo
 for ix = 1 to myCursor.num_child
  chld_name = eval('myCursor.chld'+ltrim(str(m.ix))+'_name')
  sex       = eval('myCursor.sex'+ltrim(str(m.ix))
  date_bth  = ttod(eval('myCursor.date'+ltrim(str(m.ix))+'_bth'))
  insert into children from memvar
 endfor
endscan


Cetin Basoz

ENTIRE THREAD

changing each record to one to many Posted by Philip Lawson @ 8/29/2007 6:10:02 AM
RE: changing each record to one to many Posted by Ken Murphy @ 8/29/2007 1:56:16 PM
RE: changing each record to one to many Posted by Cetin Basoz @ 8/29/2007 2:00:58 PM