Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Join or Formula problem? 1

Status
Not open for further replies.

AcctSolver

Technical User
Sep 27, 2003
28
US
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?
 
Because of the fact that the Inventory table has one record for each individual item in inventory, I don't think you're going to be able to just link the tables together to get the data you want. So, I would use a Command, which is a SQL Select statement to get ALL of the data for your report. I have a blog post that talks about how to use Commands here:
Your command might look something like this:
Code:
Select
  s.YEAR,
  s.PART_CODE,
  IsNull(i.QTY_ON_HAND, 0) QTY_ON_HAND,
  Sum(Case when s.YEAR = 2015 then s.QUANTITY_SOLD else 0 end) QTY_SOLD_2015,
  Sum(Case when s.YEAR = 2014 then s.QUANTITY_SOLD else 0 end) QTY_SOLD_2014,
  Sum(Case when s.YEAR = 2013 then s.QUANTITY_SOLD else 0 end) QTY_SOLD_2013
from SALES s
  left join (
    Select 
      PART_CODE,
      Count(*) QTY_ON_HAND
    from INVENTORY 
    group by PART_CODE
  ) I
  on s.PART_CODE = i.PART_CODE
Group By
  s.YEAR,
  s.PART_CODE,
  IsNull(i.QTY_ON_HAND, 0)

Note: This is using SQL Server syntax since you don't mention what type of DB you're connecting to. The "IsNull(...)" statement doesn't work in other databases. For example, if you're connecting to Oracle you would use NVL(...) instead.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top