How to efficiently get an account balance for every day from Transactions table?

[Thomasso]

Hi Tomasa


Are you able to upload a copy of the database?

I mean, yes, of course, but I use Microsoft SQL Server as the back end. MS Access is the front end. So I am not entirely sure what format would you prefer. Would a DDL SQL Server script be enough?

[jojowhite]

I mean, yes, of course, but I use Microsoft SQL Server as the back end. MS Access is the front end. So I am not entirely sure what format would you prefer. Would a DDL SQL Server script be enough?

you can crearte a copy of your MSAccess database.
and on the copy, create new Local tables (by using Insert Into SQL) from your Linked SQL table.
delete the Linked tables and rename your New Local Tables same name as
your previous Link tables.

you can now post the db with data.
the process is very easily created within Access.

[madpiet]

T-SQL scripts. CREATE TABLE script(s) and INSERT scripts to populate the tables. We only need enough to prove/test the query, not 2000 rows or anything silly.
We don't need any Access stuff at all. =)

[madpiet]

This part:
Account Starting Balance = 1000

2025-01-01 Transaction 1 = +100
2025-01-01 Transaction 2 = -50

Account Balance 2025-01-01 = 1050

2025-01-02 No Transactions

Account Balance 2025-01-02 = 1050

2025-01-03 Transaction3 = +500

Account Balance 2025-01-03 = 1550

This part... assume you have a Calendar that contains a date record for all days in the range (from this small example, {2025-01-01, 2025-01-02, 2025-01-03}, Then you can "force" the existence of dates by outer joining the Calendar table to your Transactions table, and then sum those transactions.

SELECT c.TheDate, TotalDeposits = COALESCE(SUM(t.DepositAmt),0), COALESCE(SUM(t.WithdrawlAmt),0)
FROM Calendar c LEFT JOIN Transactions t ON c.TheDate = t.TransactionDate
GROUP BY c.TheDate

that'll give you a single record for each date, regardless of the existence of related transaction records ( so you won't get gaps... that's what the outer join is doing ).

More when you post some data. =)

[Thomasso]

Thanks. I will post the database, however I am leaving now for 3 weeks and I don't have time to prepare the scripts. I will get back to this when I return.

[madpiet]

This appears to work... well, my numbers match up. Very likely needs good indexing, as Noella pointed out... but the logic appears to work:

USE Tempdb;
go


DROP TABLE IF EXISTS Calendar;
DROP TABLE IF EXISTS Accounts;
DROP TABLE IF EXISTS Transactions;


CREATE TABLE Calendar(TheDate DATE PRIMARY KEY);


CREATE TABLE Accounts (
	AccountID INT PRIMARY KEY,
	StartingBalance DECIMAL(8,2) NOT NULL
	);


CREATE TABLE Transactions (
	TransactionID INT IDENTITY,
	TransactionDate DATE NOT NULL,
	AccountID INT NOT NULL,
	Amount DECIMAL(8,2)
	);
GO
INSERT INTO Accounts(AccountID, StartingBalance)
VALUES (14,376.94),(15,-18629.34), (22, 200.00);


DECLARE @TheDate DATE = '2025-01-01';
WHILE @TheDate < '2025-01-12'
BEGIN
	INSERT INTO Calendar(TheDate)
	VALUES (@TheDate);
	SET @TheDate = DATEADD(day,1,@TheDate);
END;


INSERT INTO Transactions(TransactionDate, AccountID, Amount)
VALUES 
('2025-01-02', 15, -1000.00),
('2025-01-02', 15, -50.00),
('2025-01-02', 15, -169.00),
('2025-01-04', 15, -700.00),
('2025-01-06', 22, -30.00),
('2025-01-07', 22, -80.00),
('2025-01-07', 15, -15800.00),
('2025-01-07', 15, -427.41),
('2025-01-08', 14, 61477.00),
('2025-01-08', 14, -36775.75);




/* where are the accounts??! */
INSERT INTO Accounts(AccountID, StartingBalance)
VALUES
(14, 376.94), (15,-18629.34), (22,200.00);


/*Starting Balance check */
SELECT *
FROM Accounts;


/* now add the running total */
SELECT 
		daily.AccountID,
		daily.TheDate,
		daily.StartingBalance,
		daily.TransactionID,
		daily.Amount,
		rt = daily.StartingBalance + SUM(daily.Amount) OVER (PARTITION BY daily.AccountID
								ORDER BY daily.TheDate
								ROWS BETWEEN UNBOUNDED PRECEDING
									AND CURRENT ROW)
FROM
(
/* Now add transactions daily */
SELECT xj.TheDate, xj.AccountID, xj.StartingBalance, t.TransactionID, t.Amount
FROM /* cross join forces there to exist a date for every account */
	(SELECT a.AccountID, c.TheDate, a.StartingBalance
	FROM Accounts a
	CROSS JOIN Calendar c) xj
LEFT JOIN Transactions t
ON xj.AccountID = t.AccountID AND xj.TheDate = t.TransactionDate
) daily;

[jojowhite]

something must be wrong, each account on the first day of transaction, the running total is Null?

[madpiet]

something must be wrong, each account on the first day of transaction, the running total is Null?

Feel free to fix my code. Jump right in!

That's what I get for writing code in the middle of the night. You'd have to add the StartingBalance value for each account to the *first* day of the date range, and then use the running total.

[NoellaG]

Hi, try to do the running total in a separate step, something like

; with qryRT (T.tranDate,accountID,transAmount) as (select T.TransactionDate, T.accountID, A.StartingBalance + SUM(TRA.Amount) OVER (PARTITION BY T.AccountID ORDER BY T.TransactionDate) 
from v_transactions T inner join tbl1Accounts A on T.accountID = A.AccountID
where T.TransactionDate between '2025-01-01' AND '2025-01-08' and T.AccountID in (14,15,22))

select C.Date, qryRT.accountID, qryRT.transAmount
from tbl2Calender C left outer join qryRT on C.Date = qryRT.tranDate
order by C.Date

If you want to poste a copy of the SQL data, you can either post a full backup, but that would contain too much objects and data and would take a lot of time to clean, but a script to create the involved tables & views and add some data would help us to test the queries.

[jojowhite]

for my test, on MS Access, if you already have created a Linked
tables to your database (MS access database), you can Right-click on the
Linked tables and choose "Convert to Local Table".

[NoellaG]

Yep, but as the SQL uses features of T-SQL that don't exist in Access, access tables won't be of any use constructing the query.

[jojowhite]

Yep, but as the SQL uses features of T-SQL that don't exist in Access, access tables won't be of any use constructing the query.

i know msa doesnt use T-SQL, i will try to build simliar query in ms access.

for what i know (or am I wrong), the OP is using MS Access as his front end and wanted to create a Line chart (post #1) from the running balance query.
or i am wrong agan and MSSQL does have a graphing capability?

[madpiet]

Usually just connect to SQL Server from Excel and do it there.

[jojowhite]

Usually just connect to SQL Server from Excel and do it there.

ok so he is doing it in Excel.
good luck to both of you.

[Minty]

NoellaG is saying, is that you do the hard work in T-SQL on the server using the windowing functions which make running sums and similar functions very simple and efficient.

Save that as a View or Stored Procedure in SQL server and then use Access to present the data in either a report or form/graph.