Need previous month's value rolled into new month (1 Viewer)

[foshizzle]

I have a report which shows a summary of current month's inventory and transactions.I need to roll the previous ACTUAL BALANCE into the next month's report which will become the new month's BEGIN BALANCE. These numbers do not appear to be updating..

Hoping someone can point me in the right direction.
Sample DB attached. The query for the report is based on qryRSumMTD2Transactions.
You can use the report builder form called frmReportBuildSumMTD
Use dates 4/1/21 to 4/30/21 and 5/1/1 to 5/1/21 (or 5/1/1 to 5/11/21)

 

[plog]

Unlock down your database I can't get to any objects

 

[foshizzle]

Standby. I think i know the problem.

 

[foshizzle]

ok; nevermind. I still cant figure it out. Here is a fresh copy.

 

[plog]

I think you've overcomplicated this whole database. First I looked at your Relationships and I see that you have a loop: tblAirlines is connected to tblAirlineTransactions which is connected to tblFuelProviders which is connected to tblAirlines. That's incorrect--there should be only 1 way to trace a path between tables. Then I dug into qrRSumMTD2Transactions. It's built off 2 other queries, which are built off more queries which I'm assuming are built off more--I stopped trying to track things down after 2 levels.

So all I can do to help is to give you generic advice about getting balances. The simple way is to use a DSUM(https://www.techonthenet.com/access/functions/domain/dsum.php). You Sum up the field you want the balance of up to the current record's date.

Balance: DSum("[TransactionValue]", "TransactionTable", "[TransDate]<=#" & [TransDate] & "#")

The more efficeint way is to not use a DSum but a correlated subquery (https://en.wikipedia.org/wiki/Correlated_subquery). The basic idea is the same--sum up a field up to a date--but instead of the DSUM you build a whole new query.