Running Total Based on Entry No (1 Viewer)

[YAM]

Hi everyone,

I have little confusion about running total, as i have search online many are showing Sql view and not design view for running total that makes it more complicated.

SELECT QryTransactions.TransactionID, QryTransactions.TransactionDate, QryTransactions.EntryNo, QryTransactions.TransactionType, QryTransactions.AccountNo, QryTransactions.TransactionID, QryTransactions.CCY, QryTransactions.CurDebit, QryTransactions.CurCredit, Nz([CurDebit],-[CurCredit]) AS Amount, QryTransactions.TotalDebit, QryTransactions.TotalCredit, Sum([TotalDebit]-[totalcredit]) AS Balance
FROM QryTransactions
WHERE (((QryTransactions.AccountNo)=10201));

Above is Sql from my query for running total in balance field i want running total for each EntryNo separately with Ascending order.

Any help will be appreciated.

 

[arnelgp]

SELECT QryTransactions.TransactionID, QryTransactions.TransactionDate, QryTransactions.EntryNo, QryTransactions.TransactionType, QryTransactions.AccountNo, QryTransactions.TransactionID, QryTransactions.CCY, QryTransactions.CurDebit, QryTransactions.CurCredit, Nz([CurDebit],-[CurCredit]) AS Amount, QryTransactions.TotalDebit, QryTransactions.TotalCredit, DSum("[TotalDebit]-[totalcredit]", "QryTransactions","TransactionID<=" & [TransactionID]) AS RunningBalance
FROM QryTransactions
WHERE (((QryTransactions.AccountNo)=10201));

 

[YAM]

Sorry its not showing any result in RunningBalance all is blank, but it has created another TransactionID field which is sum in the botton.

Also i need running sum based on entry no and not transactionID.

 

[arnelgp]

try:

SELECT QryTransactions.TransactionID, QryTransactions.TransactionDate, QryTransactions.EntryNo, QryTransactions.TransactionType, QryTransactions.AccountNo, QryTransactions.TransactionID, QryTransactions.CCY, QryTransactions.CurDebit, QryTransactions.CurCredit, Nz([CurDebit],-[CurCredit]) AS Amount, QryTransactions.TotalDebit, QryTransactions.TotalCredit, DSum("[TotalDebit]-[totalcredit]", "QryTransactions","TransactionDate<=#" & Format([TransactionDate],"mm\/dd\/yyyy") & "# and AccountNo=10201") AS RunningBalance
FROM QryTransactions
WHERE (((QryTransactions.AccountNo)=10201));

 

[YAM]

I have attached the short version of my db it will help to understand what is going on there in order to figure out the problem.

Query name is QryStatementofAccountHomeCurrency

Please note i need running total for each transaction by AccountNo ascending with EntryNo.

 

[arnelgp]

plz try, i used function to get the running balance.

 

[YAM]

Sorry but it is giving parameter value error

below is my expression in same query named below.

RunningBalance: RunningBalance([TransactionID],[QryStatementofAccountHomeCurrency])

 

[arnelgp]

use qryTransaction

 

[YAM]

use qryTransaction

Not working i did try both ways, asking Parameter value.

Show error in result field, #Error

 

[arnelgp]

eun qryTransaction, it already has running balance on it. No need to modify.

 

[YAM]

Thanks its giving running balance for each account