Cindy,

>First of all, it's better if you name the fields specifically since the fields in common will come out with FoxPro's attempt at naming them to be different from each other.

Yeah I found that out and did some specific naming on the UPSINO fields.

Thank you so much for the bit of code below. It has taught me about a couple of new things(CREATE CURSOR and SCATTER etc), and has given me a good boost and plenty of food for thought. I'll be studying and trying this over the coming week.

Thanks again.

Elgin


>Elgin,
>
>
>I had the SQL all written and re-read the part about using 10 records and then 10 more records.
>
>

>*!* Adjust for your actual fields
>*!* Be sure to include the < other fields here > fields.
>CREATE CURSOR ReportCursor ;
>    (RecordType C(1), CustId I, UpsiNo I, ;
>    PulDat D, DgaField1 C(10), DgaField2 C(10), ;
>    VisualField1 C(10), VisualField2 C(10))
>
>SELECT NamePlatv
>SCAN && FOR SomeCondition
>
>    SCATTER MEMVAR
>
>    SELECT TOP 10 ;
>        "D" AS RecordType, ;
>        m.CustId AS CustId, m.UpsiNo AS UpsiNo, ;
>        < other fields here > ;
>        FROM Dga ;
>        WHERE Dga.CustId = m.CustId ;
>        AND Dga.UpsiNo = m.UpsiNo ;
>        AND Dga.PulDat = dSomeDate??? ;
>        INTO CURSOR TempDga ;
>        ORDER BY PulDat DESCENDING
>
>    *!* What happens if no records are selected ?
>    SELECT ReportCursor
>    APPEND FROM DBF("TempDga")
>
>    SELECT TOP 10 ;
>        "V" AS RecordType, ;
>        m.CustId AS CustId, m.UpsiNo AS UpsiNo, ;
>        < other fields here > ;
>        FROM Visual ;
>        WHERE Visual.CustId = m.CustId ;
>        AND Visual.UpsiNo = m.UpsiNo ;
>        AND Visual.PulDat = dSomeDate??? ;
>        INTO CURSOR TempVisual ;
>        ORDER BY PulDat DESCENDING
>
>    *!* What happens if no records are selected ?
>    SELECT ReportCursor
>    APPEND FROM DBF("TempVisual")
>
>ENDSCAN

>
>The RecordType is for your "Print when" in the report controls.
>
>You said that the Dga and Visual tables share PulDat in common but I think this fact may be a red herring for your data selection.
>
>See how close this comes to what you need.
>
>

---

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