Dears, I am stuck with one issue and I hope there is a solution.
I have a basically 4 fields in a query:
Ident_Code
WH_StockQty
Required_qty as BOM_Qty
Pos_Nr
I managed to show WH_StockQty in first row (MinPos_Nr) the Ident_Code appears so it doesn't repeat and value is "credited" multiple times.
I am using the code below:
Balance: (Select Sum(WH_StockQty-BOM_Qty) from CONSTR_FR_Incomplete AS Alias_Sample where Alias_Sample.Pos_Nr<=CONSTR_FR_Incomplete.Pos_Nr)
The issue is that when a different ident code comes up, the running sum adds up to the negative balance from ident code above.
Then in fact what I need is to Run Sum when the ident code is the same or re-start calculation when the WH_StockQty > 0.
Here on query below the ident code C1J6K0AC has 29 pieces but as C1J18TUX is short on 4 pieces then it calculates 29 - 4 - 2 = 23 balance but I want to show 29 - 2 = 27.
Is there a solution for this?
Ident_Code Spool_Path WH_StockQty BOM_Qty Balance Pos_Nr C1J18TUX 3-TMP-002B-362-DO-10089-01_SP01 6.000 1.00 5 920 C1J18TUX 3-TMP-002B-315-DG-00010-01_SP03 0.000 1.00 4 921 C1J18TUX 3-TMP-002D-315-HV-00040-01_SP06 0.000 1.00 3 922 C1J18TUX 3-TMP-002E-315-HC-00006-01_SP01 0.000 1.00 2 923 C1J18TUX 3-TMP-002I-362-DO-10085-01_SP01 0.000 1.00 1 924 C1J18TUX 3-TMP-002I-360-WL-10012-01_SP01 0.000 1.00 0 925 C1J18TUX 3-TMP-002I-362-DO-10045-01_SP01 0.000 2.00 -2 926 C1J18TUX 3-TMP-002I-362-DO-10085-01_SP02 0.000 1.00 -3 927 C1J18TUX 3-TMP-002I-362-DO-10089-01_SP01 0.000 1.00 -4 928 C1J6K0AC 3-TMP-002A-379-FW-10110-04_SP01 29.000 2.00 23 929 C1J6K0AC 3-TMP-002B-379-FW-10116-01_SP17 0.000 1.00 22 930 C1J6K0AC 3-TMP-002B-379-FW-10116-01_SP06 0.000 1.00 21 931 C1J6K0AC 3-TMR-005B-379-FW-10212-01_SP01 0.000 1.00 20 932 C1J6K0AC 3-TMR-005B-379-FW-10607-01_SP06 0.000 2.00 18 933 C1J6K0AC 3-TMP-002B-379-FW-10110-01_SP16 0.000 2.00 16 934