How to make Ledger Balance ( AR, AP ) in Access Query ( DO LOOp WHile LOOP)

[rehmansworld]

How to make Ledger Balance ( AR, AP ) in Access Query ( DO LOOp WHile LOOP)

I want some body to please make this balance column in QUERY.

TransactionID	TransactionDate	SupplierName	Description	Debit	Credit	Balance
			Opening Balance			5000
1	11-03-2015	ABC	Receive material	10000	0	15000
2	11-03-2015	ABC	Paid Cash	0	8000	7000
3	12-03-2015	ABC	Receive Material	4000	0	11000

I also attache simple accdb file please consider it to work for this thread.

[ranman256]

I don't believe this can be done as a query. You'd have to write the base data to a table,then loop thru changing the balance as you go.
if you put it all in a macro ,then it would have the same effect as a query.
Macro:
empty report table
append data to table
runCode LoopThruBalance()

[Bulzie]

Could you do in query as new column:

Ledger Balance: IIF([Description] = "Opening Balance",[Balance],IIF([Description] = "Receive material",[Balance]+ [Debit],IIF([Description] = "Paid Cash",[Balance]-[Credit])))

[CJ_London]

or use a subquery

SELECT *, (SELECT sum(credit-debit) FROM Sales B WHERE ID<=Sales.ID) AS Balance
FROM Sales
ORDER BY ID

[HiTechCoach]

It can be done in a query.

I use the sub query method that Ajax posted to do this all in a query.

The concept is to use a sub query to calculate the balance by summing all the previous records up to the and including the current record. There must be a field that can identify the record in sequential order. This is often the primary key if it is sequential (like an autonumber) or a date/time stamp field. For performance , it is best to use a field hat is indexed.

[rehmansworld]

Thank Yew for helping me every body , can any body please perform this in Ledger.accdb file that i attached with it , i would be very thankful to all of you.

[Bulzie]

Have not used the sub query method much but do like it, seems efficient. But in this case how is it getting the opening balance on each first Supplier record? Would they need to enter the opening balance as a Debit so the sum would pick it up?

[CJ_London]

opening balance is just a transaction so should be in sales table, not in a supplier table.

@rehmansworld - With regards modifying your db, I won't because you need to understand how it works. Happy to advise, but not to do.

[HiTechCoach]

Have not used the sub query method much but do like it, seems efficient.

You might what to check out: Subquery basics

[HiTechCoach]

Thank Yew for helping me every body , can any body please perform this in Ledger.accdb file that i attached with it , i would be very thankful to all of you.

The good online forums, like this one, are here to assist in your learning. If we did it for you it would not help you learn, just the opposite.

Give it a try. If you have trouble, feel free to ask additional questions.