Two data tables in a report

I have a situation that I just can't get to work.  It is no different from 
an invoice, where the client information comes from one table and the 
invoice details come from another.  This is a situation where retail 
insurance agents are requesting changes in a policy.  My two tables are 
"endts.dbf", which is my master table of endorsements (change
requests) and 
another called "endtdetails.dbf" which is my child table made up of
the 
various requests the insurance agents are making.  There is a one to many 
relationship between them, where several rows in the endtdetails.dbf table 
are matched to a single row in the endts.dbf table -- and the linking fields 
is called "endtnum."  I have made a datamodule (endts.dmd)  containing
these 
two tables.  I want everything in the header file to come from
"endts.dbf" 
and everything in the stream frame to come from "endtdetails.dbf"
grouped on 
the field "itemtype" which only exists in endtdetails.dbf.  No matter
what I 
have done I have not found a way to create groups on anything but fields in 
the mastertable, which is of no help at all.  How can I get it to stream 
only items from the child table?



-- 
Dan Anderson
UBI Processing Dept.
andersond@ubinc.com
800-444-4824 ext 101 

In article <ChmblFVJIHA.1316@news-server>, andersond@ubinc.com says...
> I have a situation that I just can't get to work.  It is no different from

> an invoice, where the client information comes from one table and the 
> invoice details come from another.  This is a situation where retail 
> insurance agents are requesting changes in a policy.  My two tables are 
> "endts.dbf", which is my master table of endorsements (change
requests) and 
> another called "endtdetails.dbf" which is my child table made up
of the 
> various requests the insurance agents are making.  There is a one to many 
> relationship between them, where several rows in the endtdetails.dbf table

> are matched to a single row in the endts.dbf table -- and the linking
fields 
> is called "endtnum."  I have made a datamodule (endts.dmd) 
containing these 
> two tables.  I want everything in the header file to come from
"endts.dbf" 
> and everything in the stream frame to come from "endtdetails.dbf"
grouped on 
> the field "itemtype" which only exists in endtdetails.dbf.  No
matter what I 
> have done I have not found a way to create groups on anything but fields in

> the mastertable, which is of no help at all.  How can I get it to stream 
> only items from the child table?


Dan,

I have been using one QUERY to make reports, no matter how many dBASE 
tables I have for the report. I connect them all using SQL. Here is an 
example:

sql = "select * " ;
      + "from table1 sea " ;
      + "left outer join table1table2 seam on( seam.table1_ID = 
sea.table1_ID ) " ;
      + "left outer join table2 m on( m.table2_ID = seam.table2_ID ) "
;
      + "left outer join table3 on( table3.table3_id = m.table3_id ) "
;
      + "left outer join table4 c on( c.table4_id = m.table4_id ) " ;
      + "left outer join table5 b on( b.table5_id = c.table5_id ) " ;
      + "left outer join table6 ag on( ag.table6_id = seam.table6_id ) 
" ;
      + "order by sea.table1Name, b.table5Name, c.table4Name, 
m.registrationNumber "

I have not encountered any grouping problems with this approach.

-- 
Geoff Wass [dBVIPS]
Montréal, Québec, Canada

.|.|.|        dBASE info at http://geocities.com/geoff_wass       |.|.|.
.|.|.| ---------------------------------------------------------- |.|.|.

Dan Anderson wrote:
> I have a situation that I just can't get to work.  It is no different from

> an invoice, where the client information comes from one table and the 
> invoice details come from another.  This is a situation where retail 
> insurance agents are requesting changes in a policy.  My two tables are 
> "endts.dbf", which is my master table of endorsements (change
requests) and 
> another called "endtdetails.dbf" which is my child table made up
of the 
> various requests the insurance agents are making.  There is a one to many 
> relationship between them, where several rows in the endtdetails.dbf table

> are matched to a single row in the endts.dbf table -- and the linking
fields 
> is called "endtnum."  I have made a datamodule (endts.dmd) 
containing these 
> two tables.  I want everything in the header file to come from
"endts.dbf" 
> and everything in the stream frame to come from "endtdetails.dbf"
grouped on 
> the field "itemtype" which only exists in endtdetails.dbf.  No
matter what I 
> have done I have not found a way to create groups on anything but fields in

> the mastertable, which is of no help at all.  How can I get it to stream 
> only items from the child table?

Take a good look at the streamSource's rowset property:

    this.form.streamsource1.rowset = this.form.stockord1.rowset

Make sure the streamSource's rowset is pointing to the child rowset. If 
it is not, change it in the Source Code Editor (not in the report designer).

Ken

-- 
/(Opinions expressed are purely my own, not those of dataBased
Intelligence, Inc.)/

*Ken Mayer* [dBVIPS]
/Golden Stag Productions/
dBASE at goldenstag dot net
http://www.goldenstag.net/GSP
http://www.goldenstag.net/dbase/dBASEBooks.htm