I am looking to run some simple cod

[WillieLoMain]

I am looking to run some simple code against 2 Tables.

The net result is to be a SUM of 3 columns in the 2 Tables and a 4th value wich is an addition/subtraction of the first 3.

The existing code in my app is slow. It is much fatser as I have written below but my lack of programming (which is vast) skill is giving me some trouble.

My problem is that the expression in [[[Brackets]]] below does not seem to be doing what I had hoped.

The code follows

SELECT SUM(Payment.pay), SUM(Payment.adj);
FROM Payment;
into cursor Pay

SELECT SUM(Visits.vcharge);
FROM Visits;
into cursor Vis

[[[SUM(Vis.sum_vcharge - Payment.pay - Payment.adj) TO AR]]]

SELECT SUM_pay, SUM_adj, SUM_Vcharge, AR;
FROM Pay, Vis;
to screen

In the end I get as AR the same value as SUM(Visits.vcharge)

I assume my syntax is all wrong but have no clue how to fix it.

What I realy wanted to do was make the calculation and place the value in Cursor AR.

I tried that like this:

SUM(Vis.sum_vcharge - Payment.pay - Payment.adj)
INTO Cursor AR

But that seems even more wrong and I assume there is no way to get the result of the expression Into the Cursor.

Any and all suggestions appreciated

Thanx in advance

 

[DSummZZZ]

As alway with Fox, you have a few options. But if you are just trying to get totals on the screen, do something like this:

SELECT payment
SUM pay TO ttl_pay
SUM adj TO ttl_adj

SELECT Visits
SUM vcharge TO ttl_vcharge
?'Total This: '
??ttl_pay
?'Total That: '
??ttl_adj
?'Total Theother: '
??ttl_vcharge

That will create memory variables containing the totals, and the '??' will show them on the screen. From there, do this:

?'=============================='
?'Grand Total: '
??ttl_vcharge - ttl_pay - ttl_adj



-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[advancepack]

FASTER APPLICATION ALWAYS WIN !!

Whenever you want to calculate GRAND TOTAL above post is good. But speed is depend on your number of records in dbf. If you have large dbf then you should create CASH dbf to maintain READY TOTAL vchage, pay, adj. When you require you can retrive from CASH dbf for faster SUM. This CASH dbf should always update while main DBF update Entry by Entry.

This technic I have used in my application when gives result in NO TIME.

 

[rgbean]

Dave,
I believe that using CALCULATE will be even faster (it will only need to go through all the records in Payment once):

SELECT payment
CALCULATE SUM(pay), SUM(adj) TO ttl_pay, ttl_adj

And it's possible that the following may be even faster:

SELECT .T. as Junk, sum(pay) as ttl_pay, SUM(adj) as ttl_adj ;
 FROM payment ;
 GROUP by 1 ;
 INTO CURSOR mytemp

Rick

 

[WillieLoMain]

So this is where I am at now and it looks good (and much faster then the application can do too):

SELECT payment
CALCULATE SUM(pay), SUM(adj) TO ttl_pay, ttl_adj

SELECT Visits
CALCULATE SUM(VCharge) TO ttl_charge

?'Total Payments: '
??ttl_pay
?'Total Adjustments: '
??ttl_adj
?'Total Charges: '
??ttl_charge

?'+++++=============================='
?'Accounts Receivable: '
??ttl_charge - ttl_pay - ttl_adj

A few more questions though.

1. Sum(Pay), Sum(adj) and Sum(Vcharge) print to the screen first. How to I stop that?

2. If I want to add a filter (for eg where DRNO=2 or DRNO=4)how would I add that here with correct syntax.

Cant thank you all enuff for the help

 

[rgbean]

1) SET TALK OFF
2) CALCULATE supports scoping, FOR and WHILE options. Just check the help file topic for CALCULATE for details.

Rick

 

[DSummZZZ]

Rick,

You're absolutely right. 1 pass is always better. I just wanted to post a 'sequential' solution.
In hindsight, I really should have posted both.


-Dave S.-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm