Access Query for Running Balance for Different Client Entries in Same Table

kay soo
kay soo used Ask the Experts™
on
Hi, I have hit a road block, can anyone please show me how to acquire the query result for Balance like shown in the attached jpg file from Table1?
please note the grouping ascending order is by Client Name and EntryTime

thanks in advance
QueryBalance.jpg
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Do you want to return all rows for Table1?

This should return all rows for Table1:
SELECT Date, Credit, Debit, Balance, ClientName, EntryTime
FROM Table1
ORDER BY ClientName, EntryTime;

Author

Commented:
Paul, yes, all data must be shown, the field 'Balance' should be a calculated query and shown the running results as example.
Commented:
Try this, Kay:

SELECT Table1.Date1,  Table1.credit, Table1.debit,
(Select Sum(Nz(T.Credit,0)-Nz(T.Debit,0)) From Table1 As T Where T.EntryTime <= Table1.EntryTime and T.ClientName = Table1.ClientName) AS BalanceVal, Table1.ClientName, Table1.EntryTime
FROM Table1
ORDER BY Table1.ClientName, Table1.EntryTime;

I adapted this from a solution provided by Gustav Brock in a similar question you asked in March of 2018, so Gustav gets credit!
https://www.experts-exchange.com/questions/29009998/Access-Query-to-calculate-the-running-balance-from-Credit-and-Debit-field.html#a42053937

Author

Commented:
That works just great!!!!

Tqvm for showing the query syntex.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial