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:
Table: tblAccount
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
FROM tblAccount
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)

The Result:
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...


Kenny Alligood

__________________
Kenny Alligood

Hi Kenny,
 Just send me some real data of your table with the crystal clear explanation what you exactly want to achieve. Do you want the running sum of all the records or the running sum group by intNumber or strPayee.
In your query the criteria set in Dsum function will always be true, becoz strpayee and PayeeAlias are the same thing, I mean you are not using any sub query or coorelated query.
Keep on Trying good things.


Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

Thank you for your reply and assistance. The data that I gave you in the initial post is currently the only data that I have (I am creating the database and that is test information). What I am trying to acheive is a running total of all records like:

Trans Date Amount Running Total
Opening Balance 12/1/2003 $5,000.00 $5,000.00
Deposit 1/1/2004 $10,000.00 $15,000.00
Credit Card 2/1/2004 $15,000.00 $30,000.00
ABC Supplies 3/1/2004 ($10,000.00) $20,000.00

Any ideas you may have about correcting the SQL string and accomplishing this goal would be greatly appreciated.


Kenny Alligood

Hi Kenny,

Just I'm writing you a hint and you can try this in your case.
Let's suppose a table test (a integer (Primary Key), b integer).
The record in the table as
 a b
 1 10
 2 20
 3 30
Now we want the output as
 a b running_total
 1 10 10
 2 20 30
 3 30 60

To get this I tried :

select t1.a, t1.b,(select sum(t2.b) from test t2 where t2.a <= t1.a) as running_total
from test t1 order by t1.a;

Analyse this query. Hope it will help you someway.

Regards,

Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

Thanx for the hint but it failed to produce the desired results. I really appreciate your assistance however it appears that I must find another way to accomplish this. I'm thinking exporting the data to Excel.....

Kenny Alligood

Hi Kenny,

In your case the query could be
SELECT t1.intNumber, t1.strpayee, t1.dtmDate, t1.CurAmount, (SELECT sum(t2.CurAmount) from tblAccount t2 where t2.intNumber<= t1.intNumber)
FROM tblAccount AS t1
ORDER BY t1.intNumber;
where I have considered intNumber as a primary key.
This sql string I tried basing on what I understood from your previous queries. May be I have not understood it properly.
Well what could we acheive by importing it in Excel. Actually I haven't tried this way ever.

Thanks and Regards,


Deepesh Jain
VB,VBA & .NET Specialist
Wiley Support Team

Deepesh

I really appreciate all of your assistance and ideas. As it turned out I really didn't need a running sum to begin with. However, my goal of exporting the data to Excel was centered around two facts: 1: I knew how to do it and 2: I could have Excel do all the calculating for me.

Kenny Alligood