Try this Atrix: CREATE VIEW vwAcctTranTtl (AccountNo, TransAmt) AS + SELECT AcctountNo, + SUM(IFLT(Transtate,'C',(-1*AcctIDAmount), + (IFEQ(Transtate,'D',Amount)))) + FROM TranAcctID + GROUP BY AccountNo
CREATE VIEW vwCurrAcctTotals (AccountNo, Amount) AS + SELECT AccountNo, (OrigApp + (IFNULL(TransAmt,0,TransAmt)) + FROM Accounts t1 LEFT OUTER JOIN vwAcctTranTtl t2 + ON t1.AccountNo = t2.AccountNo "Atrix Wolfe" <[EMAIL PROTECTED]> 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 > -- Albert Berry Full Time Consultant to PSD Solutions 350 West Hubbard, Suite 210 Chicago, IL 60610 312-828-9253 Ext. 32 __________________________________________________________________ McAfee VirusScan Online from the Netscape Network. Comprehensive protection for your entire computer. Get your free trial today! http://channels.netscape.com/ns/computing/mcafee/index.jsp?promo=393397 Get AOL Instant Messenger 5.1 free of charge. Download Now! http://aim.aol.com/aimnew/Aim/register.adp?promo=380455
