Add duplicate (identical) child records for 300 distinct parent records

Hello.
I have an interesting challenge for you.
A little background:
Our product uses a relational database with data stored in FoxPro, SQL, or Oracle tables.  For the tables involved in this query, the A0 table is the parent table and is related one-to-many by the BLDGCODE + RMID fields to the AS child table.  The AS table is then related back to A0 based on BLDGCODE + RMID.  There can be as many AS (child) records for one A0 record as one wishes.

A client of ours needs to duplicate the child records of one parent record (in A0 table) and assign the same 17 child records (AS table) to 300 other parent records.  The parent records are defined in the A0 table and the child records are defined in the AS table (we are using FoxPro, but the same would be true if using SQL or Oracle).

My idea is to export the 17 AS (child) records for the one A0 record into an Excel spreadsheet, and copy/paste these 17 records 300 times.  Then, (and here's where I need the help) merge in the A0 parent records to fill in the two fields (BLDGCODE + RMID) that make up the unique identifier that connects the A0 to the AS table.

Hopefully this question makes sense.  If not, please ask away.

Thank you.
fmssupportAsked:
Who is Participating?
 
ramromConnect With a Mentor consultant Commented:
Sorry, but I have not looked at the Excel sheets.

Your original specification referred to "300 other parent records". It did not say how these records were to be identified. I can only go by what you tell me.

You will need to export a FoxPro table from Excel containing (at least) BLDGCODE and RMID for these 300 parents, then change the above select to:
SELECT BLDGCODE, RMI ;
  FROM A0, table_of_300 AS TC ;
  WHERE A0.BLDGCODE  = TC.BLDGCODE  AND A0.RMID = TC.RMID ;
  INTO CURSOR parents

Open in new window

0
 
nutschCommented:
to summarize, you'd put one worksheet with the 300 parent records, one worksheet with the 17 children records and create one worksheet combining both with 5100 records. Is that correct?

0
 
ramromconsultant Commented:
Eek. That sounds like a lot of work and error prone at that.

What applications do you have available for running SQL statements? FoxPro perhaps?

Your request can easily be met by SELECT the desired records from AS  into a cursor or recordset, then INSERT them into A0, modifying the BLDGCODE + RMID  in the process. All automatic.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
felixdsouzaCommented:
Could you upload your XLS file with some sample records and expected result.  This will help in providing a solution, which should be quite simple.
0
 
fmssupportAuthor Commented:
Nutsch,
Your statement is basically correct.

Ramrom,
We have FoxPro, which I can run select statements against.  I guess my idea was that we'd export to Excel or Access and make the change, and then import back into the FoxPro database.  We also have SQL 2005 - which I can convert the database to if needed and perform functions there.  However, I am open to any ideas that you may have!

Felixdsouza:
The data should be coming soon.  I'll post it as soon as I have it.
0
 
fmssupportAuthor Commented:
The spreadsheets are attached.  The names on the files should describe them sufficiently, but let me know if you have any questions.  The Example_Merged_3rooms.xls file is the end result - but has only three rooms (A0 records) represented instead of the 300 that need to be populated.

Thank you for your assistance.
AS-project.zip
0
 
ramromconsultant Commented:
Following FoxPro code should do it.

Lemme know.
SELECT * ;
  FROM AS ;
  WHERE BLDGCODE = "xxx" AND RMID = "yyy" ;
  INTO TABLE children
 
* replace xxx with the BLDGCODE, yyy with the RMID of the desired parent 
 
SELECT BLDGCODE, RMI ;
  FROM A0 ;
  WHERE BLDGCODE + RMID != "foo" ;
  INTO CURSOR parents
 
SCAN && parents
  REPLACE BLDGCODE with parents.BLDGCODE, RMI with parents.RMI IN children
  SELECT AS
  APPEND FROM children
ENDSCAN

Open in new window

0
 
tusharkanvindeCommented:
You need a full join

SELECT BLDGCODE,RMID,GROUP,RMPERCENT FROM ROOMS FULL JOIN SPLITAS ON 1=1
0
 
fmssupportAuthor Commented:
Ramrom:
As I look at this code, it appears that I would have to run this routine 300 times - once for each BLDGCODE+RMID record in A0.  I come to this conclusion based on your comment that I have to replace xxx with the BLDGCODE, yyy with the RMID of the desired parent.

This would be just as much work as copy/pasting into Excel, in my estimation.

Let me know if I am misunderstanding something.
0
 
ramromconsultant Commented:
NO!

This is to be run ONE time to handle ALL parents.

You specified "duplicate the child records of one parent record"

The replace xxx, yyy is where you provide the id of that parent record.

The select then gets you the children records for that parent.

The SCAN loop iterates over the OTHER parent records appending the child records.
0
 
fmssupportAuthor Commented:
Thanks for the explanation, Ramrom.  I am going to try...
0
 
fmssupportAuthor Commented:
Ramrom,
In this section (see code snippet):

...shouldn't I be querying the list of parent (A0) records?  I don't want to populate for every room in the entire A0 table - just for the rooms listed in the excel doc (Rooms-A0-ParentRecs) I sent.

Thank you,

SELECT BLDGCODE, RMI ;
  FROM A0 ;
  WHERE BLDGCODE + RMID != "foo" ;
  INTO CURSOR parents

Open in new window

0
 
fmssupportAuthor Commented:
Thank you for your assistance.
0
 
ramromconsultant Commented:
Glad I could help. Please note the value of a complete problem specification. We could have solved it sooner and faster with that. That makes life easier for all of us.

I also note you did ask us to "ask away". Perhaps I could have identified the ambiguities and asked for clarification.

I also note (belatedly) that you said "we are using FoxPro)".

I guess I should take reading lessons.

Here's how I'd word the problem (from hindsight of course) (there are many ways to do this):

Table A0 (primary key BLDGCODE, RMI) is in a 1-many relationship with table AS. These tables are stored in a RDBMS that we can access using FoxPro, Excel, ....
Another table AX is in an Excel Spreadsheet. It is a 300 record subset of A0.
A particular record in A0 (BLDGCODE = "xxx" and RMI = "yyy" ) has 17 records in AS.
Goal: For each record in AX duplicate those 17 records, change the values of BLDGCODE, RMI to the values in the AX record, and insert them in AS.

Does this make sense? The spec is "lean". Minimal extra verbage. Just the facts and all of them. Even the 17 is superfluous!

Thanks for a great exercise and an opportunity to make life easier for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.