Cindy,

I'm using your suggestion on making a SELECT statement to gather the data for my report. However I'm having problems with the JOIN portion, I think. I have to pull data from 3 tables. The parent is VIEW called Nameplatv, and the two child tables are Dga and Visual. All three have the CUSTID and UPSINO in common. I'm trying to pull records from the DGA table that meet a custid criteria then pull the same related records from the Visual. I first tried pulling all data with one SELECT and two JOINs. In another thread, someone told me to do it with two SELECT statements. I have tried that as shown below. When I do the SELECT for the one-to-many between the Nameplatv and Dga, no problem the data is correct, approx 462 records. When I try to add the Visual table, I get a result set of over 4000 records. I should have approximately the same amount of records. The DGA and Visual have the custid, upsino, and puldat fields in common. I have tried variations on the JOIN order, the ORDER BY , and the GROUP BY commands. Anyway here are the SELECT statements.

SELECT * FROM  nameplatv LEFT JOIN dga ;
   ON  nameplatv.custid+nameplatv.upsino = dga.custid+dga.upsino;
   INTO cursor dganame ;
   ORDER BY Dga.upsino, Dga.puldat DESC

SELECT * ;
   FROM dganame INNER JOIN visual ON dganame.custid+dganame.upsino= ;
      visual.custid+visual.upsino ;
   INTO CURSOR rdga ;
   WHERE dganame.custid=gcCustid ;
   ORDER BY dganame.upsino ASC, dganame.puldat DESC

I'm trying to select records where I will print the Fields of the Nameplatv as the header, the last ten records for each UPSINO in the dga table as the first section of detail, and the last ten records for each UPSINO in the Visual table as the second section of detail. I know I'm going to have to do something with the "print when" parameters for this, but I'll worry about that after I get my select statement correct.

Your help is much appreciated.

Thanks
Elgin

>Most reports are started from a form where the user chooses some type of criteria. In the CLICK() of the "go" button (or some method it calls) I SELECT all the data for the report, exactly the way I want it, into one non-normalized cursor.

>So, in the CLICK() of the button, the code would look something like this:
>

>SELECT Parent.ParentFields, Child.ChildFields ;
>    FROM Parent LEFT JOIN Child ;
>    ON Parent.ParentKey = Child.ForeignKey ;
>    INTO CURSOR ReportCursor ;
>    WHERE < some criteria > ;
>    AND < some other criteria > ;
>    ORDER BY < some order >
>



>*!* Some of my reports have changable titles
>lcReportTitle = "Records meeting criteria"
>*!* I have some fields that I don't always show, using Print When
>llShowSalary = .T.
>
>REPORT FORM MyReport TO PRINTER NOCONSOLE
>USE IN ReportCursor
>

>This method of approaching reports is also used by several other people who hang out here and do a lot of reports.
>
>"I have to design this report to a specific format."
>I'm not sure how using the report's environment or not would affect how your report is "designed" if by "designed" you mean the way the report controls, groupings, headers, horizontal lines, page footers, etc. are laid out. It would, however, greatly change things if there were a design requirement laid down by someone else to put data in the environment or whatever.
>
>
>
>
>>Cindy,
>>
>>"Never make reports from related tables"? How do I design a report that is based on a one to many relationship? Do I create a view establishing that relationship between the parent and child tables, then place that view in the data environment of the report?
>>
>>I already have the parent and child views established in the form that will be calling this report. The form also establishes the relationship between the parent and the child views.
>>
>>Are you telling me to not use the data environment of the report designer? I have to design this report to a specific format.
>>
>>I think I can extract the data using a SELECT statement as you described below, but how do I use my report format and where does that SELECT statement go?
>>
>>Thanks
>>Elgin