×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Displaying lines based on a variable

Displaying lines based on a variable

Displaying lines based on a variable

(OP)
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!






RE: Displaying lines based on a variable

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Displaying lines based on a variable

(OP)
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!

RE: Displaying lines based on a variable

(OP)
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!


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close