Displaying lines based on a variable

[vshvidky]

Hi there,

I have 2 tables: STOCK table and STOCKTRANSACTIONS table. The stock table shows me how much product I have in every bin right now. STOCKTRANSACTIONS have every product transaction recorded (could be in multiple bins). I am trying to display all the latest transactions that apply to a certain bin, up to the qty in that bin.

For example, I have product ABC in bin DMG. STOCK table shows QTY = 5 in that location. I can link both the bin location and the product to the STOCKTRANSACTIONS table. I see that there were 20 transactions for the item ABC in the last year. I am trying to display only the latest transactions that will equal to the QTY in that bin location (5).

STOCK
ITM: ABC QTY: 5 BIN: DMG
ITM: DFE QTY: 1 BIN: DMG

STOCKTRANSACTIONS
TXN: 6 ITM: ABC BIN: DMG DATE: 07/23/2019 QTY: 3
TXN: 5 ITM: ABC BIN: DMG DATE: 07/21/2019 QTY: -1
TXN: 4 ITM: ABC BIN: DMG DATE: 07/21/2019 QTY: 2
TXN: 3 ITM: ABC BIN: DMG DATE: 07/19/2019 QTY: 1
TXN: 1 ITM: ABC BIN: DMG DATE: 07/17/2019 QTY: 1
TXN: 2 ITM: DFE BIN: DMG DATE: 07/18/2019 QTY: 1

I have everything grouped by the STOCK.ITM and have a running total for the QTY in the details and in the DESC order from the STOCKTRANSACTIONS table. Basically, I am looking for something where once the STOCK.QTY = Running Total QTY, it stops printing records. What would be the easier way to accomplish this?

Thank you very much for your help!

 

[SkipVought]

Hmmmmm?

TXN 4 on 7/19: OH Qty = 1
TXNs 2, 3, & 5 on 7/21: OH Qty = 3
TXN 1 on 7/23: OH Qty = 6

???

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[vshvidky]

Sorry, made it confusing with transaction numbers, trying to simplify the issue.

TXN 5 is for a different product(DFE), so OH QTY for DFE is 1. And adding other transactions for ABC, you get OH QTY = 5. Each transaction just shows the adjustment for the product at that transaction, not how much product is in the bin. Does it make sense? Thanks!

 

[vshvidky]

OK, I edited OP to make it easier to understand. Hopefully, it makes more sense now.

So my STOCK.QTY of ABC in bin DMG is 5. I need to show only the latest transactions that will match the qty of stock. So while there are 5 records in the STOCKTRANSACTIONS for ABC, I need it stop printing records once the total is equal to STOCK.QTY of ABC (5). So the output would be only 4 latest records, because the total QTY in the transactions would be 5, equaling STOCK.QTY:

TXN: 6 ITM: ABC BIN: DMG DATE: 07/23/2019 QTY: 3
TXN: 5 ITM: ABC BIN: DMG DATE: 07/21/2019 QTY: -1
TXN: 4 ITM: ABC BIN: DMG DATE: 07/21/2019 QTY: 2
TXN: 3 ITM: ABC BIN: DMG DATE: 07/19/2019 QTY: 1

It would also print the only record for the product DFE, because the STOCK.QTY = 1 and the only transaction for it has QTY of 1.

TXN: 2 ITM: DFE BIN: DMG DATE: 07/18/2019 QTY: 1

Thanks!