I suggest a UNION with a GROUP BY. Without a subquery, you can create a temporary cursor. Something like this:
SELECT CustNo, Sales, .T. as DummyField FROM Table1;
UNION ALL SELECT CustNo, Sales, .T. as DummyField FROM Table2;
INTO CURSOR Temp
SELECT CustNo, sum(Sales) as Sales;
FROM Temp INTO CURSOR TempSummary
I believe VFP 5 doesn't have the NOFILTER clause; therefore I included an extra field, which should produce the same effect.
>Hi All:
>
>Please consider the following:
>
>
>Table1:
>
>Custno Sales
>ABCD 40000
>XYZS 20000
>
>Table2:
>
>Custno Sales
>XYZS 100
>YYYY 7000
>
>
>I want to come up with SQL that will generate a cursor called cursor1 with total sales for each customer:
>
>
>Cursor1:
>
>Custno TotalSales
>ABCD 40000
>XYZS 20100
>YYYY 7000
>
>
>Note that custno ABCD is absent from table2, XYZS is present in both and YYYY is absent from table1. Also, please note that this is for VFP 5, so nesting SELECTs may not work.
>
>Thanks,
>
>Yossi
Difference in opinions hath cost many millions of lives: for instance, whether flesh be bread, or bread be flesh; whether whistling be a vice or a virtue; whether it be better to kiss a post, or throw it into the fire... (from Gulliver's Travels)