Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 23rd, 2003, 12:25 PM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default DSum() Issues

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
 
Old December 25th, 2003, 03:57 AM
Authorized User
 
Join Date: May 2003
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 27th, 2003, 06:43 PM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 29th, 2003, 08:27 AM
Authorized User
 
Join Date: May 2003
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 29th, 2003, 11:00 AM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old December 30th, 2003, 05:15 AM
Authorized User
 
Join Date: May 2003
Posts: 62
Thanks: 0
Thanked 0 Times in 0 Posts
Default


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
 
Old January 5th, 2004, 07:04 PM
Authorized User
 
Join Date: Jun 2003
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
connection string issues, web.config file issues kaliaparijat ASP.NET 2.0 Professional 1 June 12th, 2008 08:07 AM
IE Issues iPagan BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 3 April 1st, 2007 04:11 AM
Still Having Issues slas7713 BOOK: ASP.NET 2.0 Website Programming Problem Design Solution ISBN: 978-0-7645-8464-0 9 October 27th, 2006 02:59 PM
Issues with xp_cmdshell ninel SQL Server 2000 1 August 26th, 2006 09:39 AM
For-each issues hiskeyd XSLT 1 February 27th, 2006 08:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.