Running Balance if same material codes

[gstreichanbr]

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

[ranman256]

in the report, have a group on CODE,
in the text box properties (data tab): field BALANCE, set RUNNING SUM = OVER GROUP

[Welshgasman]

You need to include the Ident_Code in the criteria?

[gstreichanbr]

Yes, Welshgasman. I have done what ranman256 informed but if I could do it straight on query then would be perfect.
This query is in fact appending on a table completing the information. This query just shows incomplete items with stock lower than total required.

[Welshgasman]

No I meant you need to include the code as criteria in the query. Nothing to do with any report.

[gstreichanbr]

Yes, I need to include the code as criteria in the query. If you can help, I will be appreciated.

[Welshgasman]

Try

Select Sum(WH_StockQty-BOM_Qty) from CONSTR_FR_Incomplete AS Alias_Sample where Alias_Sample.Pos_Nr<=CONSTR_FR_Incomplete.Pos_Nr AND Alias_Sample.Ident_Code = Ident_Code )

where Alias_Sample.Ident_Code is the field in your table to match. I am just guessing the name here.

[gstreichanbr]

Thanks a lot Welshgasman! Worked beautifully! All the best!