Union All For More Than Two Table!

[RAJESHKUMAR R]

Dear Experts,

In the Attached File, there is a Union Query Called as “For_Firm_UNION” which Right now pull the Data From FirmOrder & Forecast Table! Which is Working Fine!

Now, I would like to add “SHIPMENT_FILTER” Query output also below Current Result of For_Firm_UNION Query! ie Product, Qty, CRD YrMo & Cat as “Shipment”

Please suggest SQL Query which will give Desired Result!

SELECT Product, Qty, CRD, Format([CRD], "yyyy_mm") AS YrMo, "FO" AS Cat FROM Firmorder
UNION ALL SELECT Product, Qty*-1, CRD, Format([CRD], "yyyy_mm"), "FC" FROM Forecast;

Thanks & Regards,
Rajeshkumar R

[ranman256]

just add it to the end of the union, BUT it MUST have the exact # fields AND fieldTypes. (but not Names):

SELECT Product, Qty, CRD, Format([CRD], "yyyy_mm") AS YrMo, "FO" AS Cat FROM Firmorder
UNION
SELECT Product, Qty*-1, CRD, Format([CRD], "yyyy_mm"), "FC" FROM Forecast
union
SELECT Product, Qty, CRD, Format([CRD], "yyyy_mm"), "XX" FROM [SHIPMENT_FILTER]

[RAJESHKUMAR R]

Dear Ranman,

Thanks it works amazingly!

Thanks & Regards,
Rajeshkumar R