Aggregating the data is easy enough and they appear like in my report. Where I'm having trouble is with the running sum, I can't figure out how to use DSum() to calculate my balance
In my query results should look like this
Code:
Vendor_Name |
Component_ID |
Current_Stock |
Supply |
Demand |
Planned_Date |
Balance |
VendorX |
Item123 |
0 |
44000 |
0 |
16-Aug-19 |
44000 |
VendorX |
Item123 |
0 |
0 |
43554 |
21-Aug-19 |
446 |
VendorX |
Item123 |
0 |
0 |
518.5 |
01-Sep-19 |
-72.5 |
VendorX |
Item123 |
0 |
0 |
31110 |
01-Sep-19 |
-31183 |
VendorX |
Item456 |
27066 |
0 |
0 |
|
27066 |
VendorX |
Item456 |
0 |
20000 |
0 |
24-Jul-19 |
47066 |
VendorX |
Item456 |
0 |
0 |
27000 |
26-Jul-19 |
20066 |
VendorX |
Item456 |
0 |
0 |
20000 |
06-Aug-19 |
66 |
Except "Balance" is empty at the moment
Balance is a running sum of [Current_Stock]+[Supply]-[Demand]