Daily Account balance

[jasn_78]

Hey guys I have a table I am trying to write a query for that will give me a closing balance for an account on each day.

Now I have no control over the table and its fields as its a 3rd party database

I am ok calculating the starting opening balance by just adding all sales and minusing all receipts for all days before the opening date

however what i have no idea on is how to recalculate in a query to show the closing balance for that same day.

if any1 has any suggestion it would be appreciated

Jason

 

[The_Doc_Man]

Usually, this takes a "divide and conquer" approach.

Write a query to GROUP BY date and generate a sum of transactions for each date.

Then layer a second query on top of that to generate the running sum. You can look up "Running Sum" to see how that is done. Or you can just write that second query to do a DSUM() for the field that is the daily sum, using as criteria that it must show you the DSUM of the field for all dates less than or equal to the date of the group-by record in question. Look up DSUM to see how that is done.

 

[jasn_78]

Doc_Man
I have written the below query with dsum but i get the same value on all dates

What am i missing?

SELECT qryACCDAILYTRX.ACCOUNT, 
	DSum("TRXAMT","qryACCDAILYTRX",
		"ACCOUNT Between Forms![frmACCTRX]![txtACCFROM] and Forms![frmACCTRX]![txtACCTO] 
			and ACOP_TRX_DATE Between Forms![frmACCTRX]![txtDATEFROM]  
			and  Forms![frmACCTRX]![txtDATETO]") AS RUNNING, 
	qryACCDAILYTRX.ACOP_REF, 
	qryACCDAILYTRX.ACOP_TRX_DATE
FROM qryACCDAILYTRX
GROUP BY qryACCDAILYTRX.ACCOUNT, 
	DSum("TRXAMT","qryACCDAILYTRX",
		"ACCOUNT Between Forms![frmACCTRX]![txtACCFROM] 
			and Forms![frmACCTRX]![txtACCTO] 
			and ACOP_TRX_DATE Between Forms![frmACCTRX]![txtDATEFROM]  
			and  Forms![frmACCTRX]![txtDATETO]"), 
		qryACCDAILYTRX.ACOP_REF, qryACCDAILYTRX.ACOP_TRX_DATE;

 

[jasn_78]

Doc_Man dont worry bout it mate thanks for your help found using the running total option on a textbox on the report works great

jason