SELECT Fld1, SUM(Ammount) AS Ammount1; FROM crsTest1; GROUP BY Fld1; INTO CURSOR cTest1 SELECT Fld1, SUM(Ammount) AS Ammount2; FROM crsTest2; GROUP BY Fld1; INTO CURSOR cTest2 SELECT crsTest.Fld1,; cTest1.Ammount1,; cTest2.Ammount2; FROM crsTest; INNER JOIN cTest1 ON crsTest.Fld1 = cTest1.Fld1; INNER JOIN cTest2 ON crsTest.Fld1 = cTest2.Fld1>This is the solution that I was trying to avoid:
>>Fld1 Test1.Ammount Test2.Ammount >>--------------------------------------- >>1 10 100 >>1 11 100 >>>>You got as many records as the largest number of records in all tables. In VFP9 you could use derived tables to got the result you want, I am not sure about VFP8 (can't test it right now).
>>SELECT crsTest.Fld1, cTest1.Amnt AS Ammount1, cTest2.Amnt AS Ammount2; >> FROM crsTest; >>INNER JOIN (SELECT Fld1, SUM(Ammount) AS Amnt; >> FROM crsTest1; >> GROUP BY Fld1) cTest1; >>ON crsTest.Fld1 = cTest1.Fld1; >>INNER JOIN (SELECT Fld1, SUM(Ammount) AS Amnt; >> FROM crsTest2; >> GROUP BY Fld1) cTest2; >>ON crsTest.Fld1 = cTest2.Fld1 >>>>