ACCESS 2007 Running Balance in query

  • Thread starter Thread starter jodi
  • Start date Start date
J

jodi

Hi,

I have a problem in putting a running balance in my query.. I have no
idea about it because im new to acces 2007.

I just need to subtract the Debit to the credit while having a running
balance.

Can anyone help me? Please, i really need it!

Here's a screenshot of my query. Thank You!


http://img.photobucket.com/albums/v631/angeljocelyn/HELP.jpg


Right now here is the SQL code that i have in my query:

SELECT [Copy Of General Journal].Month, [Copy Of General Journal].Day,
[Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit, DSum("Nz(Debit)-Nz(Credit)","[Copy Of General
Journal]") AS Expr1
FROM [Copy Of General Journal] INNER JOIN [Chart of Accounts Table] ON
([Copy Of General Journal].[Account #] = [Chart of Accounts Table].
[Account Code]) AND ([Copy Of General Journal].[Account Title] =
[Chart of Accounts Table].[Account Titles])
GROUP BY [Copy Of General Journal].Month, [Copy Of General
Journal].Day, [Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit
HAVING ((([Copy Of General Journal].[Account #])="112"));


Thank You!
 
Hi,

I have a problem in putting a running balance in my query.. I have no
idea about it because im new to acces 2007.

I just need to subtract the Debit to the credit while having a running
balance.

Can anyone help me? Please, i really need it!

Here's a screenshot of my query. Thank You!

http://img.photobucket.com/albums/v631/angeljocelyn/HELP.jpg

Right now here is the SQL code that i have in my query:

SELECT [Copy Of General Journal].Month, [Copy Of General Journal].Day,
[Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit, DSum("Nz(Debit)-Nz(Credit)","[Copy Of General
Journal]") AS Expr1
FROM [Copy Of General Journal] INNER JOIN [Chart of Accounts Table] ON
([Copy Of General Journal].[Account #] = [Chart of Accounts Table].
[Account Code]) AND ([Copy Of General Journal].[Account Title] =
[Chart of Accounts Table].[Account Titles])
GROUP BY [Copy Of General Journal].Month, [Copy Of General
Journal].Day, [Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit
HAVING ((([Copy Of General Journal].[Account #])="112"));

Thank You!
Click to expand...

Maybe something like this?

SELECT [Account #], Explanation, Sum(Nz(Debit)-Nz(Credit)) AS Balance
FROM [Copy Of General Journal]
WHERE [Account #]="112"
GROUP BY [Account #], Explanation;


Groeten,

Peter
http://access.xps350.com
 
On Sat, 3 Apr 2010 04:14:22 -0700 (PDT), jodi

This is easy to do in a Report, because it has built-in support for
that. See the textbox' RunningSum property.

-Tom.
Microsoft Access MVP
 
On Sat, 3 Apr 2010 04:14:22 -0700 (PDT), jodi


This is easy to do in a Report, because it has built-in support for
that. See the textbox' RunningSum property.

-Tom.
Microsoft Access MVP


Hi,
Click to expand...
I have a problem in putting a running balance in my query.. I have no
idea about it because im new to acces 2007.
Click to expand...
I just need to subtract the Debit to the credit while having a running
balance.
Click to expand...
Can anyone help me? Please, i really need it!
Click to expand...
Here's a screenshot of my query. Thank You!

Right now here is the SQL code that i have in my query:
Click to expand...
SELECT [Copy Of General Journal].Month, [Copy Of General Journal].Day,
[Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit, DSum("Nz(Debit)-Nz(Credit)","[Copy Of General
Journal]") AS Expr1
FROM [Copy Of General Journal] INNER JOIN [Chart of Accounts Table] ON
([Copy Of General Journal].[Account #] = [Chart of Accounts Table].
[Account Code]) AND ([Copy Of General Journal].[Account Title] =
[Chart of Accounts Table].[Account Titles])
GROUP BY [Copy Of General Journal].Month, [Copy Of General
Journal].Day, [Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit
HAVING ((([Copy Of General Journal].[Account #])="112"));
Click to expand...
Thank You!
Click to expand...
Click to expand...

how? , i tried to research but i cant find how to run a running
balance in the report where in i could subtract debit to credit..
 
On Sat, 3 Apr 2010 09:17:45 -0700 (PDT), jodi

In the query for the report, add a column like this:
Balance: Credit - Debit

Then in the report, put this field on it, and set the RunningSum
property.

-Tom.
Microsoft Access MVP

On Sat, 3 Apr 2010 04:14:22 -0700 (PDT), jodi


This is easy to do in a Report, because it has built-in support for
that. See the textbox' RunningSum property.

-Tom.
Microsoft Access MVP


Hi,
Click to expand...
I have a problem in putting a running balance in my query.. I have no
idea about it because im new to acces 2007.
Click to expand...
I just need to subtract the Debit to the credit while having a running
balance.
Click to expand...
Can anyone help me? Please, i really need it!
Click to expand...
Here's a screenshot of my query. Thank You!

Right now here is the SQL code that i have in my query:
Click to expand...
SELECT [Copy Of General Journal].Month, [Copy Of General Journal].Day,
[Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit, DSum("Nz(Debit)-Nz(Credit)","[Copy Of General
Journal]") AS Expr1
FROM [Copy Of General Journal] INNER JOIN [Chart of Accounts Table] ON
([Copy Of General Journal].[Account #] = [Chart of Accounts Table].
[Account Code]) AND ([Copy Of General Journal].[Account Title] =
[Chart of Accounts Table].[Account Titles])
GROUP BY [Copy Of General Journal].Month, [Copy Of General
Journal].Day, [Copy Of General Journal].[Account #], [Copy Of General
Journal].Explanation, [Copy Of General Journal].Debit, [Copy Of
General Journal].Credit
HAVING ((([Copy Of General Journal].[Account #])="112"));
Click to expand...
Thank You!
Click to expand...
Click to expand...

how? , i tried to research but i cant find how to run a running
balance in the report where in i could subtract debit to credit..
Click to expand...
 
Back
Top