AcctSolver
Technical User
I have two tables, SALES and INVENTORY. In INVENTORY, I have PART_CODE and QUANTITY_ON_HAND. There is a record for each individual item; if I have 25 in stock of an item, I have 25 records. In SALES I have YEAR, PART_CODE and QUANTITY_SOLD. I have a QUANTITY_SOLD for each item I have sold. If I have sold 40 of a particular item, I have 40 records. My desired output is a report with 5 columns. PART_CODE, QTY_ON_HAND, QTY_SOLD_2015, QTY_SOLD_2014, QTY_SOLD_2013. All of these to be summed by part code. I have two separate reports where it works fine for each table. Select SALES.PART_CODE, YEAR, QUANTITY_SOLD. I have formulas for the years; if YEAR=2015 then QUANTITY_SOLD, else 0. Same thing for the other 3 years. I put them on the report, grouped by part code, then summarized. Output just what I was looking for. For the Inventory report, select INVENTORY.PART_CODE, INVENTORY.QUANTITY_ON_HAND. I put the part code and the qty on hand on the report, grouped by part code and summarized. Also what I was looking for. I tried joining the INVENTORY table to the SALES table on part code, and performing the same logic with part code linked. That did not work; in my detail section I was getting one line for each sale multiplied by the quantity of items per part code.
How do I get them to behave distinctly?
How do I get them to behave distinctly?