Sum information into a cursor

[bebbo]

The code below works like a treat. Table Cardt and Postr stores history records of clients. These clients are represented by a card number, stored in a table called "card". Problem I have the history could be in too other tables Cardtran and Postranh how would I gather this information in the same statement. I know I could run something similar to below using the different tables then combine the two results. However can I check cardt, postr, cardtran, and postanh all together????

Note, card numbers in "card" are unique. These represent a client and are use in cardt and cardtran. Cardt fileds are the same as cardtran and postr fields are the same as postranh, just different date ranges.

SELECT cardt.cardnum, ;
SUM(IIF(cardt.amount>0,cardt.amount,0.00)) as Credit , ;
SUM(IIF(cardt.amount<0,cardt.amount,0.00)*-1) as Debit ;
INTO cursor tmpAMOUNT ;
FROM Cardt, Postr ;
WHERE PADL(cardt.POSID, 2,'0')+PADL(cardt.postran,2,'0');
= PADL(POSTR.POSID, 2, '0') + PADL(POSTR.TRANS, 10,'0') ;
AND POSTR.DATE >= FromDate ;
AND POSTR.DATE <= ToDate ;
AND POSTR.STATUS = 0 ;
GROUP BY cardt.cardnum

Thanks!!!

 

[TomasDill]

It is not possible in VFP. This is because when you add another table to the query, and that table will have more than one record for each record in the current query, sum will be incorrect just because values for sum will be get twise or more times for a single record, and as a result sum will be much greater. The solution with 2 temporary cursors joined later into a single one is good. Vlad Grynchyshyn, MVP
vgryn@softserve.lviv.ua

http://www.softservecom.com

The professional level of programmer could be determined by level of stupidity of his/her bugs

 

[bebbo]

Thanks, I thought that may be the case.