COMBINE SQL 2

[johnny45]

Hello
using the following query to get qty SOLD for 2009:

SELECT OEINVD.ITEM, Sum(OEINVD.QTYSHIPPED) AS SUMSOLD
FROM OEINVH INNER JOIN OEINVD ON OEINVH.INVUNIQ = OEINVD.INVUNIQ
GROUP BY OEINVD.ITEM, OEINVH.INVFISCYR
HAVING (((OEINVH.INVFISCYR)="2009"));

and the following to get RETURNS for 2009:

SELECT OECRDD.ITEM, SUM(OECRDD.QTYRETURN) AS RETURNS
FROM OECRDH INNER JOIN OECRDD ON OECRDH.CRDUNIQ = OECRDD.CRDUNIQ
WHERE (((OECRDH.INVFISCYR)="2009"))
GROUP BY OECRDD.ITEM

my question, ca these 2 qyeries be combined into one to get the NET (SOLD less RETURNS) by ITEM ????

 

[ettienne]

Why beat yourself to death?

SELECT OESHDT.ITEM, Sum(OESHDT.QTYSOLD) AS SumSOLD
FROM OESHDT
GROUP BY OESHDT.ITEM, OESHDT.YR
HAVING OESHDT.YR="2009";

 

[ettienne]

You can also use

SELECT OESHHD.ITEM, Sum(OESHHD.QTYSOLD) AS SumSOLD
FROM OESHHD
WHERE OESHHD.YR="2009"
GROUP BY OESHHD.ITEM;

 

[Jay Converse]

Also look at OEAUD*

 

[johnny45]

Thank you both for demystifying the accpac tables for me.

Cheers,
Johnny

 

[DjangMan]

But just to answer your first question you would use the UNION or UNION ALL sql command. That will let you combine the results of two or more queries together.

The workarounds presented, however, are far better than using a UNION.