I need some assistance in getting a running total of a Checking Account Register working. I feel that I have tried everything and so far no success.
Here is what I am working with:
Field1: intNumber (Long)
Field2: strPayee (Text)
Field3: dtmDate (Short Date)
Field4: strMemo (Text)
Fieild5: curAmount (Currency)
What I would like to happen is for a running total to be generated so that I can add this data to a listbox in a form that looks and acts just like a check register in Quicken/MS Money.
What I have tried:
SELECT tblAccount.intNumber, strPayee AS PayeeAlias, tblAccount.dtmDate, tblAccount.strMemo, Sum(tblAccount.curAmount) AS SumOfcurAmount, Format(DSum([tblAccount].[curAmount],"tblAccount",[strPayee]<=[PayeeAlias]),"$0,000.00") AS RunTot
GROUP BY tblAccount.intNumber, strPayee, tblAccount.dtmDate, tblAccount.strMemo, tblAccount.curAmount
ORDER BY tblAccount.dtmDate;
Here is the data in the Table:
intNumber strPayee dtmDate strMemo curAmount
Opening Balance 12/1/2003 $5,000.00
Deposit 1/1/2004 $10,000.00
Credit Card 2/1/2004 $15,000.00
101 ABC Supplies 3/1/2004 whatever ($10,000.00)
intNumber strPayee dtmDate strMemo curAmount RunTot
Opening Balance 12/1/2003 $5,000.00 $20,000.00
Deposit 1/1/2004 $10,000.00 $40,000.00
Credit Card 2/1/2004 $15,000.00 $60,000.00
101 ABC Supplies 3/1/2004 whatever ($10,000.00) -$40,000.00
These number are not correct and I would appreciate any assistance you could offer. I have already looked at what Microsoft has to offer at http://support.microsoft.com/default.aspx?kbid=208714
to no avail. By the way -- I am using A2K on WinXP.
Thanx in advance folks...