Solved

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

Posted on 2008-06-11
14
647 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 

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 400 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

710 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