Running balance in MS access

Question

1.00/5 (1 vote)

See more:

SQL-Server-2016

Hi,

I have a database that I created with my basic knowledge. I want to ask whether somebody could assist with a SQL statement for a running balance. I tried several proposed SQL statements, but it seems to not work.
Pardon me if I cross the line with my request.
Regards

What I have tried:

I created a table with a budgeted amount
Then I created another table with expenses
I tried to create a query which I want to indicate the available balance.
It was not successful; from there my request for assistance.

Posted 15-May-20 7:24am

Member 14833529

Add a Solution

Comments

ZurdoDev 15-May-20 13:28pm

How can we help?

Member 14833529 15-May-20 14:42pm

1. I have a total budget amount (for a period, eg. 3 months) under its own column and an expenditure column for all expenses. All these are in one table.
2. I have to provide a running balance in my report on how I spent the funds.

ZurdoDev 15-May-20 14:45pm

You just repeated yourself. That does not help. If you want someone to help, it would be helpful to post what you have done, what your structure looks like, what data looks like and how you expect the data to come out.

In other words, remember that we can't see anything that you have so just telling us what you want to have happen sounds like you are asking someone to do all the work for you.

Member 14833529 15-May-20 14:53pm

Ok. I am sorry. I did not want you to do all the work for me. That is why I explained what I tried to do....and I also looked to similar questions on the forum and tried answers provided. Thanks anyway. I will try to figure it out myself. Kindest regards.

ZurdoDev 15-May-20 14:59pm

You missed my point. How can we possibly tell you how to find the available balance when we have no idea how you are storing your data? You have a table with budgets and a table with expenses.

So, the best we can tell you, unless you give us more info, is you need to subtract your expenses from your budget. Probably. We have no idea what you are doing.

It's kind of like calling your mechanic on the phone and stating that you want to drive your car but you can't. You have to give more info.

Thousands of people are willing to help you but you need to give enough info for anyone to do that.

Member 14833529 15-May-20 15:12pm

You made me smile...but, it is true.
The table outlay is as follows:

ID Date_Opened Credit_Details Payment_From (CO) Debit_Details Payment_To (CO) Credit_Amount Debit_Amount
1 13-May-2020 Sales of Heifers; Agra Auction A32055057 Agra None None $21,326.00 000
2 14-May-2020 Refreshments for trip to farm Charney Spar $0.00 (352)
3 14-May-2020 Diesel Shell, Mariental $0.00 (500)
4 14-May-2020 Purchase at Agra, fodder, etc Agra, Mariental $0.00 (5,640)

NB: The first row only has the credited amount of N$21,326. All other rows are having debit entries. I hope it is clear now.

ZurdoDev 15-May-20 15:14pm

No, it is not clear. You mentioned you had 2 tables but now you only show one. As MadMyche also suggested, click Improve Question so that everyone can see this easier and show what you expect for results.

I'm sure your problem is not that hard but I still do not know how to help you.

Member 14833529 15-May-20 15:24pm

ZurdoDev. I said I tried 1st with 2 different tables, but changed my mind. That is why I tried to do it in one table under different columns and to use a query to calculate deductions (debits) and additions (credits). Maybe its a language barrier which is now at play. Anyway, I really appreciate your valuable efforts to try to help me.

ZurdoDev 15-May-20 15:25pm

Well, if they are in 2 different columns, it's very easy. Subtract the sum of one column from the sum of the other.

Member 14833529 15-May-20 15:28pm

Thanks ZurdoDev.

MadMyche 15-May-20 15:07pm

Try using the Improve Question widget to add in the relevant table definition, some sample data, AND what you expect for results

Member 14833529 15-May-20 15:17pm

Thanks, I tried to illustrate with the copied entries in my reply to ZurdoDev. It is almost the same as for a bank account, where you receive a salary or income and spend; from which you have a running balance which will change as future credits and debits will occur.

MadMyche 15-May-20 16:23pm

Your condensing into 1 table looks like it was just appended on the ends
I would recommend something like this

ID  Date_Opened   TransType   Amount      Party          Details
===========================================================================================
1   13-May-2020   Credit      21,326.00   Agra Auction   Sale of Heifers A32055057   
2   14-May-2020   Debit       (352.00)    Charney Spar   Refreshments for trip to farm
3   14-May-2020   Debit       (500.00)    Mariental      Diesel Shell
4   14-May-2020   Debit       (5,640.00)  Mariental      Purchase at Agra, fodder, etc Agra

Member 14833529 16-May-20 2:27am

Good Day MadMyche,
Thanks for your reply. It is now morning time here in our country. Perhaps there where you are might still be night time. Your reply is valuable. I see that you arranged all amounts under one column. I also understand what you propose. However, having a limitation of knowledge on Access SQL and VBA I cannot add it up as if it was in MS Excel. The table in your reply is the usual Excel table. However, I am not sure whether one can apply it as such. I will copy to you my last trial, last night. It is kind of similar but slightly different.

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14833529 · Answer 1 · 2020-05-15T20:49:00

Dear MadMyche,
A copy of my query follows:

ID	Date_Transaction	Credit_Details	Company	Debit_Details	Credit_Amount	Debit_Amount	Balance
1	13-May-2020	Sales of Heifers; Agra Auction A32055057	Agra	None	$21,326.00	0	$21,326.00
2	14-May-2020		Spar Mariental	Refreshments for trip to farm	$0.00	(352)	($352.18)
3	14-May-2020		Shell Mariental	Diesel	$0.00	(500)	($500.00)
4	14-May-2020		Agra	Purchase fodder, etc	$0.00	(5,640)	($5,640.00)
5	14-May-2020		Family	Profit sharing	$0.00	(1,000)	($1,000.00)
					$21,326.00	(7,492.18)	$13,833.82

Running balance in MS access

1 solution

Solution 1

Add your solution here

Preview 0

Running balance in MS access

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us