Solved

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

Posted on 2008-06-11
14
611 Views
Last Modified: 2013-11-15
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.
0
Comment
Question by:fmssupport
14 Comments
 
LVL 39

Expert Comment

by:nutsch
Comment Utility
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
 
LVL 17

Expert Comment

by:ramrom
Comment Utility
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
 
LVL 9

Expert Comment

by:felixdsouza
Comment Utility
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
 

Author Comment

by:fmssupport
Comment Utility
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
 

Author Comment

by:fmssupport
Comment Utility
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
 
LVL 17

Expert Comment

by:ramrom
Comment Utility
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
 
LVL 14

Expert Comment

by:tusharkanvinde
Comment Utility
You need a full join

SELECT BLDGCODE,RMID,GROUP,RMPERCENT FROM ROOMS FULL JOIN SPLITAS ON 1=1
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:fmssupport
Comment Utility
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
 
LVL 17

Expert Comment

by:ramrom
Comment Utility
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
 

Author Comment

by:fmssupport
Comment Utility
Thanks for the explanation, Ramrom.  I am going to try...
0
 

Author Comment

by:fmssupport
Comment Utility
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
 
LVL 17

Accepted Solution

by:
ramrom earned 400 total points
Comment Utility
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
 

Author Closing Comment

by:fmssupport
Comment Utility
Thank you for your assistance.
0
 
LVL 17

Expert Comment

by:ramrom
Comment Utility
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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
identifying alphanumerics in a column 6 20
Query output to Excel producing error 7 34
Convert .PDF 6 38
MIN, using ARRAY 4 0
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now