MS Excel Query problem

[techwriterAR]

I have a query in Microsoft Excel that returns carton usage information for a period of time. I also need to list the parent product for each carton. This information is listed in 3 tables. I have created one query that returns the carton usage and one that returns the parent-child product list. If I use vlookup to match product in carton usage list with child in parent-child list, the results are inaccurate. The parent-child list shows that some cartons are used with more than one parent product. Is there a way to combine these two queries so I get the parents that use each carton??


Find carton usage query:

SELECT A.PRDNO,SUM(B.QUANT),month(b.ittdt)
FROM ROPMAST604 A INNER JOIN INITP100X B ON (A.PRDNO=B.PRDNO) WHERE B.TCODE = 'BI' AND A.PALPH = 'CARTON' AND (YEAR(B.ITTDT)*100 + MONTH(B.ITTDT))>=200604 AND (YEAR(B.ITTDT)*100 + MONTH(B.ITTDT))<200704)
GROUP BY A.PRDNO, B.ITTDT


Find parent-child product query:

select a.parnt,a.child,b.palph from pspsp100x a inner join ropmast604 b on (a.child=b.prdno) where b.palph = 'CARTON'

 

[SkipVought]

Hi,

Here's a try...

SELECT
  PMA.PRDNO
, PMC.CHILD
, SUM(BOM.QUANT)
, month(bOM.ittdt)
    
FROM       ROPMAST604 PMA 
INNER JOIN INITP100X  BOM 
   ON (PMA.PRDNO=BOM.PRDNO) 
INNER JOIN ROPMAST604 PMC  
   ON (PMC.CHILD=BOM.PRDNO) 

WHERE BOM.TCODE = 'BI'
  AND PMA.PALPH = 'CARTON'
  AND (YEAR(BOM.ITTDT)*100 + MONTH(BOM.ITTDT))>=200604
  AND (YEAR(BOM.ITTDT)*100 + MONTH(BOM.ITTDT))<200704)

GROUP BY
  PMA.PRDNO
, PMC.CHILD
, BOM.ITTDT

Skip,
[sub]
[red][/red]
[/sub]