Hi, I want to pull data from two tables, and I want to filter the data from one of those sources, but not the other.
From table one (which contains inventory balances only), I simply want to extract Prod#, and ProdQty.
From table two (which contains inventory transactions only), I want Prod#, and ProdQty, for any product with Work Order haveing status code of "A" for active.
So far, my trouble has stemmed from the fact that while the first table is balance data, and has only one balance per product, the second table is transaction data, and may have many transaction quantities per product, so when I put the two extracts in columns of one report, the balance data duplicates for every instance of a transaction of the same product number.
So I wind up with:
QtyBal QtyTrans
ProdA 100 80
ProdB 50 20
ProdC 150 100
Total 300 200
I actually wind up with:
QtyBal QtyTrans
ProdA 100 30
ProdB 50 5
ProdC 150 30
ProdC 150 5
ProdC
150 25
Total 950 200
I want to bring in the transaction data in such a way that it is already summarized, rather than per transaction, but the only way that has been suggested for me to do this is with a Stored Procedure, which I am not able to produce.
I have tried to simply suppress duplicates in the Balance collumn, but when I try to sum the data, it still picks up the ones that are suppressed. I then tried a running total to add them correctly, which does seem to work, but then when I get to the part about filtering only for a status of "A" on the transaction side (which would indicate they are still on an open workorder), I wind up also filtering the OnHand Balances too, which I don't want to. It is as if it has a link to the same status code, which it should not. I am wondering if there is a command I could set up that would allow me to get some of this done therein.
Any suggestions?
Thanks,
Paul
Edited by pgiering - 24 Nov 2009 at 3:11pm
IP Logged
