Solved How to Create a Running Total Sum Query in Microsoft Access

[Safari]

Hi All

I Want to Create a Running Balance as Total Sum in Ms Access Query
I Tried Many Times But Not Working

can any one help me and edit the query " Aging" in my attached DB
Query name : Aging

i want new column named NetBalance showing me the running balance for each record in the query

 

[theDBguy]

Have you seen this one?

Create a running totals query - Microsoft 365 Apps

Demonstrates two methods that you can use to create a running totals query in Microsoft Access.

docs.microsoft.com

 

[Safari]

its not clear for me
still need some help

 

[SHANEMAC51]

its not clear for me
still need some help

SELECT TblHeadk.Nz, TblHeadk.da, TblGL.AccId, TblGL.AccName,
 TblGL.cer3, TblGL.deb3, TblGL.des3,
 DateDiff("d",[Da],Date()) AS InvoiceAging,
 nz([deb3],0)-nz(TblGL.cer3,0) AS m00,
 IIf([InvoiceAging] Between 1 And 30,[deb3],0) AS [1-30],
 IIf([InvoiceAging] Between 31 And 60,[deb3],0) AS [31-60],
 IIf([InvoiceAging] Between 61 And 90,[deb3],0) AS [61-90],
 IIf([InvoiceAging] Between 91 And 120,[deb3],0) AS [91-120],
 IIf([InvoiceAging]>120,[deb3],0) AS [Over 120]
FROM TblHeadk INNER JOIN TblGL ON TblHeadk.nz = TblGL.nz1;

SELECT Aging.da, Aging.Nz,
 DSum("m00","aging","Aging.da<=" & Format(aging.da,"\#mm\/dd\/yyyy\#")) AS sum11, Aging.m00,
 Aging.AccId, Aging.AccName, Aging.cer3, Aging.deb3, Aging.des3, Aging.InvoiceAging,
 Aging.[1-30], Aging.[31-60], Aging.[61-90], Aging.[91-120], Aging.[Over 120]
FROM Aging
ORDER BY Aging.da, Aging.Nz;

da	Nz	sum11	m00	AccId	AccName	cer3	deb3
01.01.2022	4	10900	10900	102030201010102		0,00	10 900,00
22.02.2022	3	13080	2180	102030201010102		0,00	2 180,00
23.02.2022	7	2140	-5940	102030201010102		5 940,00	0,00
23.02.2022	6	2140	-5000	102030201010102		5 000,00	0,00

 

[gemma-the-husky]

Personally, my feeling is to find a way to work without this.
A running sum is not how a database works. A database works on the basis of an overall total of the set of records.
As a result it's much easier to use what you can do easily.

A report view will give you a running total easily, so provide that functionality with a report.

 

[Pat Hartman]

its not clear for me

If you don't have two monitors, print out the directions and try to follow them using your own table.

I agree with Dave, Running sums do NOT belong in a query. They belong in a report.

And finally, for running sum to work in a query, you MUST have a unique identifier on which to base them. You don't need a unique identifier in a report because the report is a sequential process. One line at a time so it is trivial to keep a running sum. Queries work very differently.

 

[Safari]

SELECT TblHeadk.Nz, TblHeadk.da, TblGL.AccId, TblGL.AccName,
TblGL.cer3, TblGL.deb3, TblGL.des3,
DateDiff("d",[Da],Date()) AS InvoiceAging,
nz([deb3],0)-nz(TblGL.cer3,0) AS m00,
IIf([InvoiceAging] Between 1 And 30,[deb3],0) AS [1-30],
IIf([InvoiceAging] Between 31 And 60,[deb3],0) AS [31-60],
IIf([InvoiceAging] Between 61 And 90,[deb3],0) AS [61-90],
IIf([InvoiceAging] Between 91 And 120,[deb3],0) AS [91-120],
IIf([InvoiceAging]>120,[deb3],0) AS [Over 120]
FROM TblHeadk INNER JOIN TblGL ON TblHeadk.nz = TblGL.nz1;

SELECT Aging.da, Aging.Nz,
DSum("m00","aging","Aging.da<=" & Format(aging.da,"\#mm\/dd\/yyyy\#")) AS sum11, Aging.m00,
Aging.AccId, Aging.AccName, Aging.cer3, Aging.deb3, Aging.des3, Aging.InvoiceAging,
Aging.[1-30], Aging.[31-60], Aging.[61-90], Aging.[91-120], Aging.[Over 120]
FROM Aging
ORDER BY Aging.da, Aging.Nz;

da	Nz	sum11	m00	AccId	AccName	cer3	deb3
01.01.2022	4	10900	10900	102030201010102		0,00	10 900,00
22.02.2022	3	13080	2180	102030201010102		0,00	2 180,00
23.02.2022	7	2140	-5940	102030201010102		5 940,00	0,00
23.02.2022	6	2140	-5000	102030201010102		5 000,00	0,00

IF YOU PLEASE CAN YOU SEND ME THE DB WITH EDIT TO CHECK THE CODES BECAUSE I WANT TO SEE IT HOW IS IT WORKING

 

[SHANEMAC51]

IF YOU PLEASE CAN YOU SEND ME THE DB WITH EDIT TO CHECK THE CODES BECAUSE I WANT TO SEE IT HOW IS IT WORKING

I removed the column signatures, so it's clearer to me