PDA

View Full Version : Problem of running sum in query and report


adnanbpl
Jan 24, 2012, 04:43 AM
With the help of Internet Search Engine I found your name as a tutor of ms access. Can you help me to solve my problem?

I just want Running Sum in a Query in my daily transactions of the customer FILTER BY ACCOUNT NUMBERS seprately. The fields of which we are calculating is:
CLOSING BALANCE = OPENING BALANCE BILL AMOUNT - PAYMENT RECEIVED

I am sending you my database structure for the ready reference. Through Dsum Command I am trying to display thr running some but not susscess. Let me see my formula of Dsum kindly correct them.

CLOSING BALANCE: DSum("[OPBAL] [BILL AMOUNT]-[PAYRCVD]","[LEDGER]","[ACCNO]=" & [ACCNO] & " AND [TID]<=" & [SLNO])


Kindly help me to sort out the problems

Thanking you,

Yours faithfuly,
Smaaq.

ScottGem
Jan 24, 2012, 04:52 AM
Have you checked out these tutorials on running sums?

Running Sum Download Tutorial (http://www.blueclaw-db.com/download/running_sum_query.htm)
ACC2000: How to Create a Grouped Running Sum in a Query (http://support.microsoft.com/kb/205183)

You should not have a balance field in your tables. To calculate a running sum, you need to add the incoming amounts and subtract the outgoing ones. So using a DSum is correct, but not the way you are using it.

Check out those articles and, if you have more specific questions, please ask.