Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Ken Murphy
  Where is Ken Murphy?
 Springhill
 Canada
 Ken Murphy
 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: 143520 # Views: 1 # Ratings: 0
Version: Not Applicable Category: Databases, Tables and SQL Server
Date: Wednesday, August 29, 2007 1:56:16 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

Philip,

This is a bit of a design problem. The first table you show is not normalized so it makes querying the data difficult. A better design would be:

CREATE TABLE People (RecordID I AUTOINC, FirstName C(30), Surname C(30), GenderID I, BirthDate D)
CREATE TABLE Genders (RecordID I AUTOINC, GenderName C(10))
CREATE TABLE Family (RecordID I AUTOINC, Address C(30), City C(30), Province C(2), PostalCode C(10), ...)
CREATE TABLE FamilyMembers (RecordID I AUTOINC, PeopleID I, FamilyID I, MemberTypeID)
CREATE TABLE MemberType (RecordID I AUTOINC, TypeName C(10)) && Parent, Child, etc.


With a more normalized data structure, you can then run a single query over the data and get what ever information you want.  In this case, if you want a list of all of the children,

SELECT People.*, Genders.GenderName ;
   FROM FamilyMembers ;
   INNER JOIN People ON People.RecordID == FamilyMembers.PeopleID ;
   INNER JOIN MemberType ON MemberType.RecordID = FamilyMembers.MemberTypeID ;
   WHERE MemberType.TypeName = [Child]
   INTO CURSOR csrChildren


With the design you have, you are going to have to create a series of queries (one for each "child" field in your first table):
SELECT Child1_Name AS ChildName, Sex1 AS Sex, Date1_Bth AS BthDate ;
   FROM MyTable WHERE NOT EMPTY(Child1_Name) INTO CURSOR Child1
SELECT Child2_Name AS ChildName, Sex2 AS Sex, Date2_Bth AS BthDate ;
   FROM MyTable WHERE NOT EMPTY(Child2_Name) INTO CURSOR Child2
SELECT Child3_Name AS ChildName, Sex3 AS Sex, Date3_Bth AS BthDate ;
   FROM MyTable WHERE NOT EMPTY(Child3_Name) INTO CURSOR Child3
...

Then you are going to have combine them.
CREATE CURSOR csrChildren (ChildName C(30), Sex C(10), BthDate D)
APPEND FROM DBF([Child1])
APPEND FROM DBF([Child2])
APPEND FROM DBF([Child3])
...


If this is a large table, you are going to have to make a pass over it for each child so this could end up taking a fair bit of time. The other way to do it is to use a SCAN/ENDSCAN loop.

CREATE CURSOR csrChildren (ChildName C(30), Sex C(10), BthDate D)
SELECT MyTable
SCAN
   IF NOT EMPTY Child1_Name
      INSERT INTO csrChildren VALUES (Child1_Name, Sex1, Date1_Bth)
   ENDIF
   IF NOT EMPTY Child2_Name
      INSERT INTO csrChildren VALUES (Child2_Name, Sex2, Date2_Bth)
   ENDIF
   IF NOT EMPTY Child3_Name
      INSERT INTO csrChildren VALUES (Child3_Name, Sex3, Date3_Bth)
   ENDIF
      ...
ENDSCAN

The code itself would be quite a bit slower than a query, but as it only has to take one pass over the table, it may be considerably quicker than running a series of queries.

Ken
You shall know the truth - and the truth shall set you free. (John 8:33)

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