Running Sum Help

[Heather2005]

I have a report that I want to work like a check register. The user enters data into a form, date, amount, whether it is a check or a deposit etc.

The report is based on a query with a parameter for the date and I want it to calculate the sum in the report for a specified amount of time. I can use running sum but it only sums the dates that are entered. I need it to sum everything up until the final date that I enter. How can I make this happen?

I hope this makes sense. I can try to explain another way if it does not.

So the user needs to open the report, type in a beginning and an end date and should see a report that has a total which is gotten from the balance for the previous entries up until the end date requested by the user.

Thanks,
Heather

 

[jazminecat]

Do you have a field that is storing the balance for each entry, or do you have the balance stored elsewhere? You need to have the beginning balance somewhere on the report, so that the calculation at the end can use it.

 

[Heather2005]

Yes, I have a balance field in the table that the report uses.

The problem is I want to view data from start date to end date but I want it to balance from the start of the dates in the table to the end date specified for the report. Does that makes sense?

 

[jazminecat]

One way to accomplish this would be to put a field on the report to store the beginning balance from the first record. Use a query to populate this field with the balance from your table. Then, depending on how your report is laid out, put a calculation field in the group footer or the report footer, that calculates the end balance, starting with the beginning balance from the top of the report. Use the =Sum function in a text box to calculate this ending balance. Hope this helps!

 

[Heather2005]

That is kind of where I was going with that already. My difficulty is that I have a parameter for the query and I only want the user have an ending sum up until the end date that the user specifies.

 

[jazminecat]

Can you post your sql for the query? Also, what are the results you are currently seeing when you run it. It seems like if your query allows the user to select the beginning and ending date, and if the report then shows only those dates, the sum should be what you want. Is the report turning out to show more data than the parameters that are set in the query? If so, the problem is with the query and not the report calculation. Let us know what you're getting, and I bet someone here will be able to spot the trouble.