check book balance

[johnlatona123]

so i am trying to make a fancy version of a checkbook transaction register with access. i then realized that i have no idea how to write a calculated field that can add or subtract based on the balance of the previous record.

anyone able to help me out here?

 

[BBQ Kittens]

This may guide you in the right direction.
http://support.microsoft.com/kb/290136

 

[Alansidman]

Here's a template for a checkbook that I wrote a couple of years ago. Look at how I did the running sums in the queries. When you download and open the zip file, be sure to put both files in the same directory for it to work properly.

 

[johnlatona123]

thanks for the suggestions but i guess we are going to have to call me dense on this one....

ive got this going as a calcuated field in a query of my check book transactions table:

balance: DSum("amount","checkbook")

for reference sake: new field name: dsum("field","table")

this is resulting in an additive funciton and all of my records are showing the same "balance" value rather than showing some type of incrementation from the addition or subtraction of funds.

maybe a more direct explanation of the Dsum function would help me?

help?

 

[Alansidman]

The Dsum does not give you a running sum. If used in a query, it will return the same result in each record

Did you look at my example. I use the DSum in the main form. For a running sum balance, I created a query and based the report on that query. You will need to look at the query called qryTransactions and in particular, look at the SQL statement as it is a little complex and you won't see how it all comes together in the QBE. The report is called Check Register.

Look at this and in particular thread #8
http://www.mrexcel.com/forum/showthread.php?t=358307&highlight=Running+Sums

Alan

 

[johnlatona123]

thanks for the help, but im still stuck
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT [tbl_Orig].[UniqueKey], (SELECT Sum([tbl_Orig].[FieldToSum]) AS Total
FROM [tbl_Orig]
WHERE ((([tbl_Orig].[UniqueKey])<=[tbl_Alias].[UniqueKey]))

AS Total
FROM [tbl_Orig] AS [tbl_Alias];

Before going back to the normal view, change all occurences of 'tbl_Orig' with the name of your table, all occurences of 'UniqueKey' with the name of your unique key in the table and the one occurence of field to sum with the field to be totalled.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

table name is checkbook
field to sum is amount
and i guess i dont get what the unique key and tbl_alias should be?

 

[Alansidman]

In your case try this:

SELECT [checkbook].[DateField], (SELECT Sum([checkbook].[Amount]) AS Total
FROM [checkbook]
WHERE ((([checkbook].[DateField])<=[tchkbk].[DateField])) AS Total
FROM [checkbook] AS [tchkbk];

You will need to put this in your query in the SQL dropdown. Change the field name to your date field name.

 

[johnlatona123]

please see attachment of my error, im sorry to burden you with this, but im not all that handy with sql

 

[Alansidman]

Ok. Missing a closed parenthesis. I think that it goes just before As Total

in this line of code

WHERE ((([checkbook].[DateField])<=[tchkbk].[DateField]))[COLOR="Red"])[/COLOR] AS Total

 

[johnlatona123]

ok woot we got that going now! thanks a ton alan!

now next question is, how do i make my "amount" field become a negative number based upon the word selected in a seperate combo box

 

[Alansidman]

I would urge you to use two fields, one for debits and one for credits, then you don't have to deal with that issue. Otherwise, you will have to develop some VBA in the afterupdate event to multiply the value entered by -1 if a certain value is in the combo box.

 

[johnlatona123]

im open to ideas, though i think my wife would prefer the simpler 1 box solution like the after update thing....

any ideas how i can structure that method to meet my needs?

i want my [amount] field to change to a negative value if my combo box states "withdrawl"

also, can you explain more how you would structure it to do the two fields method?

thanks!

 

[Alansidman]

For the field that you are posting the deposits and withdrawals, in the after update event put this code:

Private Sub Count1_AfterUpdate()
If Me.Type = 2 Then
Me.Count1 = Me.Count1 * (-1)
End If
End Sub

The field "Type" is a combo box with two values, ID=1, value= deposit, ID =2, Value = Withdrawal.

The field count1 holds the amount of deposit or withdrawal. Change these field names to what is in your db.

If you look at my checkbook that I attached earlier you will see how I used two fields for withdrawals and deposits.