Putting all detail records in one field 
Author Message
 Putting all detail records in one field

Please help if you have ANY ideas.
I have a 1:M relationship.  Because I need to create a report using Word
(or wordperfect) I have to denormalize these tables.  I need to create a
query which lists in a new field all records in one field in the detail
table related to the master record.

Thus, for example, if I have a client table keyed to client # and and
contact table keyed to client # and contact #.  These tables are linked
1:M.  I need a query which creates 1 record for each client, but with an
extra field which consists of the following structure: contact1,
contact2, contact3, etc. for each contact related to the respective
client.  A "comma" would separate each contact name and a period would
end the field.

Thanks in advance.

By the way, I can do this with a report but I cannot transfer it to a
Word or WordPerfect file, which is needed.

Andy



Sat, 16 Oct 1999 03:00:00 GMT
 Putting all detail records in one field



Quote:

> Please help if you have ANY ideas.
> I have a 1:M relationship.  Because I need to create a report using Word
> (or wordperfect) I have to denormalize these tables.  I need to create a
> query which lists in a new field all records in one field in the detail
> table related to the master record.

> Thus, for example, if I have a client table keyed to client # and and
> contact table keyed to client # and contact #.  These tables are linked
> 1:M.  I need a query which creates 1 record for each client, but with an
> extra field which consists of the following structure: contact1,
> contact2, contact3, etc. for each contact related to the respective
> client.  A "comma" would separate each contact name and a period would
> end the field.

> Thanks in advance.

> By the way, I can do this with a report but I cannot transfer it to a
> Word or WordPerfect file, which is needed.

> Andy

I have used the following solution for a similar problem (buildings with
many floors and many technical descriptions)

1. select the detail records with queries (q1, q2, q3 producing 3 answer
files)
2. create indexes on the answer tables
3. scan the master table and the detail tables and compose a memo field
with the needed separators ( comma, tabular, carriage return ...)
4. write the memo to a file (with a .doc extension)

If you use tabulars for the detail tables, it is possible to format the
text in word and convert the detail informations in a table format.

I hope this can help you.

method run(var eventInfo Event)
var
        tb table
        q query
        tc1, tc2, tc3 tcursor
        m       memo
endvar

setMouseShape(mouseWait)

q.readFromFile("q1.qbe")
q.executeQBE()
q.readFromFile("q2.qbe")
q.executeQBE()
q.readFromFile("q3.qbe")
q.executeQBE()

tb.attach(":priv:answer1.db")
index tb        primary on "Property "endindex
tb.attach(":priv:answer2.db")
index tb        primary on "Property "endindex
tb.attach(":priv:answer3.db")
index tb        primary on "Property "endindex

tc1.open(":priv:answer1.db")
tc2.open(":priv:answer2.db")
tc2.switchIndex("Property #")
tc3.open(":priv:answer3.db")
tc3.switchIndex("Property #")

scan tc1 :
        message("property :"+string(tc1.recno())+"/"+string(tc1.nrecords()))
        m =  memo(

                          string(tc1.(1)) + "\n"
                        + string(tc1.(2)) + "\n"
                        + string(tc1.(3)) + "\n"
                        + string(tc1.(4)) + " "
                        + string(tc1.(5)) + "\n"
                        + string(tc1.(6)) + "\n"
                        + string(tc1.(7)) + "\n"
                        + string(tc1.(8)) + "\n"
                        + tc1.(9) + "\n")

                        m = m + memo("\nFloor\tUsage\tAreas")

                        scan tc2 for tc2.(1) = tc1.(1):
                                m = m + memo("\n"
                                                + string(tc2.(3)) + "\t"
                                                + string(tc2.(4)) + "\t"
                                                + string(tc2.(5)) + " "
                                                + string(tc2.(6)))
                        endscan

                        m = m + memo("\n")

                        scan tc3 for tc3.(1) = tc1.(1):
                                m = m + memo("\n"
                                                + string(tc3.(3)) + "\t"
                                                + string(tc3.(4)))
                        endscan

        m.writetofile("T"+format("w6,ez", tc1."property #")+".doc")

endscan

setMouseShape(mouseArrow)

endMethod



Sat, 16 Oct 1999 03:00:00 GMT
 Putting all detail records in one field

Quote:
> Please help if you have ANY ideas.
> I have a 1:M relationship.  Because I need to create a report using
> Word (or wordperfect) I have to denormalize these tables.  I need to
> create a query which lists in a new field all records in one field in
> the detail table related to the master record.

Not a query. What you need is a scan loop.

Daniel A. Morgan



Sat, 16 Oct 1999 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. putting multiple records in a list in one record

2. master - detail in one row, details in one extra column

3. Changing Key Fields in Master Record without Orphaning Detail Records

4. Using BULK INSERT to put a file into ONE field

5. Exporting Detail records into one Table

6. Need Help: search/replace data in one field/one record, by queries or script: PDOX5

7. Master / detail form - more than one link fields

8. How to copy a field from one record to a related record in the same database

9. PDOX4.5--Comparing fields between records (except ONE field)

10. a simple problem putting a record field !

11. Sorting detail records in a master detail relation.

12. Problem when inserting records in the detail table (master/detail relation)


 
Powered by phpBB® Forum Software