Running Total in Access report (1 Viewer)

[bmhuettinger]

I have a query that shows the open order quantity by month and a table that show projected ship pounds (based on production capacity). I also have a query that totals the amount of backlog pounds (previous month or earlier). I need to be able to add the backlog pounds to the current month, subtract the projected ship pounds, and use that total as the starting "backlog" for the next month. Then add next month's orders, less projected shipments, and so on...

backlog through April - 100,000
plus May Orders - 50,000
less May projected shipments - 20,000
=130,000 starting backlog for June
plus June Orders - 50,000
less projected shipments - 25,000
=155,000 starting backlog for July

The obvious issue is that i can't refer to same the text box in the report because each month's total is based on the previous month's new backlog. I've tried using the running sum to no avail. And i don't think that I can unlink text boxes in a column of data.

I'm not sure how to share my report/queries but I'm willing to upload, as I'm probably not articulating the problem concisely enough.

 

[theDBguy]

Try exporting your query into Excel and then create a new sheet to show how you want that query result to show up in the report.
Just a thought...

 

[bmhuettinger]

hi - thanks for the response. I've exported my report into Excel and have a created a "corrected" column next to the incorrect data. I've also shown the expression that I'm currently using in access to get the incorrect data. I'm unable to attach an Excel spreadsheet, however. Were you suggesting that I upload the sheet for someone to analyze, or were you suggesting I just attempt to create this "report" in Excel? If it can't be done in Access, I'll abandon the mission.

 

[theDBguy]

I was suggesting you post the file here. Try zipping it up first.

 

[CarlettoFed]

To attach a file you must use the "Attach Files".

 

[Gasman]

To attach a file you must use the "Attach Files".

Excel files are not shown in the allowed extensions and if you try and upload one you get as below. So you need to rename or zip ?

 

[bmhuettinger]

Got it! Thanks everyone! Txt file attached.

 

[theDBguy]

Got it! Thanks everyone! Txt file attached.

Hi. Thanks. I was actually hoping to get a copy of the data to play with, so we can tell you how to build the report you wanted.