Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-11
14
Medium Priority
?
661 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 21765222
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
ID: 21765809
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
ID: 21768102
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 

Author Comment

by:fmssupport
ID: 21768854
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
ID: 21770023
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
ID: 21770510
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
ID: 21770570
You need a full join

SELECT BLDGCODE,RMID,GROUP,RMPERCENT FROM ROOMS FULL JOIN SPLITAS ON 1=1
0
 

Author Comment

by:fmssupport
ID: 21771280
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
ID: 21771383
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
ID: 21773144
Thanks for the explanation, Ramrom.  I am going to try...
0
 

Author Comment

by:fmssupport
ID: 21773555
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 1600 total points
ID: 21774880
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
ID: 31466311
Thank you for your assistance.
0
 
LVL 17

Expert Comment

by:ramrom
ID: 21779201
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

730 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