Atrix, I think this might help get you there.
CREATE VIEW AtrixTranSummary + (AcctID, OrigApp, BalanceChange, NewBalance) + AS SELECT + t2.AcctID, + a1.OrigApp, SUM (IFEQ(TranState,'D', (AcctIDAmount), 0) - + IFEQ(TranState,'A', (AcctIDAmount), 0) - + IFEQ(TranState,'B', (AcctIDAmount), 0)), + SUM (OrigApp + IFEQ(TranState,'D', (AcctIDAmount), 0) - + IFEQ(TranState,'A', (AcctIDAmount), 0) - + IFEQ(TranState,'B', (AcctIDAmount), 0)), + FROM Accounts a1, TranAcctID t2 + WHERE TranState <> 'C' + AND (a1.AcctID = t2.AcctID) + GROUP BY t2.AcctID Bill On Wed, 30 Jul 2003 15:25:38 -0700, Atrix Wolfe wrote: >hey guys, > >I have a problem im working on and was wondering if theres a way to do >it more efficient than i am > >I have 2 tables, One stores account numbers, their unique ID and their >starting balance > >Table Accounts: >Accountno text 50 >Acctid integer >OrigApp currency > >The other stores acctids, an amount for a transaction and a transaction >type of A,B,C or D. > >Table TranAcctid: >Acctid integer >AcctidAmount currency >TranState text 1 > >The balance of an account = OrigApp - TranAcctid(A's) - TranAcctid(B's) >+ TranAcctid(D's) > >so A and B are subtracted, C is ignored and D is added... > >The way im doing it now is joining the accounts table with tranacctid >and manualy looping through the results with witango and calculating the >balances that way...then after that i have to go back to the database to >get all the accounts that dont use transactions (arent in tranacctid) >and display those. > >kinda a pain and pretty darn slow! > >is there a way to get the balance of all the accounts with a single sql >statement? Some kind of crazy join and group by or something? > >Thanks for the help! >Atrix
