Bob, I think you need two things to do this.
1) A date/time stamp or autonum (or equiv) column to establish the order of entry. 2) A stored procedure that acts as an accumulator for the select or view. Your example didn't use neg numbers for payments, but I'm guessing that's a typeo. If not, the stored procedure could do the math by checking for your "Payment" notation. You would set a beginning value in a variable, probably 0. Your select would be ordered by the autonum column. The only small problem is I don't recall how to call the procedure in the select <blush>, but Dr R> had given a great example of this some months ago, so either he or someone else might chime in. Also; I _think_ you would be limited to one customer at a time, though the procedure might be made smart enough to recognize the change in customer ID's and reset the accumulator to 0. Good luck, Ben Petersen On 6 Jun 2002, at 16:59, Bob Thompson wrote: > I have a scenario that perhaps you all could assist with > on using a view instead of a DECLARE CURSOR routine. > Any help would be greatly appreciated if possible! > > A table has columns among others, (TICKET#), (CUST#) and > (INV_AMT). The (INV_AMT) column contains both charges > and payments with payments being a negative number and > charges being positive. > > When a customer makes a purchase of $100, there is an > entry into the table for TICKET#, CUST# and $100. When > they make a payment, an entry of Cust# and $-100 is > entered and the balance is therefore (0). > > The question is: Can a view be created that will show all > transactions back to a zero balance? The example below > is data for one CUST# only: > > TICKET# INV_AMT (Actual running balance) > 1 $100 $100 > 2 $150 $250 > Payment $100 $150 > Payment $150 $0 > 3 $100 $100 > 4 $75 $175 > Payment $75 $100 > > I want the view to show : > > TICKET # INV_AMT > 3 $100 > 4 $75 > Payment $75 > > This may sound a little strange, but I have a requirement > where I need to show transactions on an ongoing basis > back to where customer accounts have a zero balance. > Unlike more standard systems where you close a month out > and simply carry a balance forward, this report must show > all transactions back to a "zero balance point" and this > point can change daily. I believe it would be much more > efficient if a view could handle this rather than using a > DECLARE CURSOR routine, but I have been unable > to come up with one so far. > > Thank you all for your consideration! > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l > ================================================ > TO SEARCH ARCHIVES: > http://www.mail-archive.com/rbase-l%40sonetmail.com/ > ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l ================================================ TO SEARCH ARCHIVES: http://www.mail-archive.com/rbase-l%40sonetmail.com/
