Hi there!
I am preparing a data set which resides in two database tables associated by a common attribute ("CPN").
Table A (warehouse qty on hand):
[ProdID]
[QOH]
Table B (products needed):
[ProdID]
[Deadline]
[QTY] (always = 1)
Order By [Deadline] asc
I need to conduct a lookup for each row between Table A and Table B and for each row to subtract [QTY] from [QOH].
Table B is ordered by [Deadline] as I need the products with the closest deadline to be treated first.
Imagined outcome:
Option 1) A flag in Table B which indicates if QOH for a ProdID has reached zero. I will then be able to create a list of products we're able to produce with the current warehouse QOH stats.
Option 2) A column in Table B stating what the adjusted QOH is for the record's ProdID (okay if it's a negative number).
Hope someone is able to crack the case.
