SUM IIF with Multiple Crosstab Queries, Table & Query

[RAJESHKUMAR R]

Dear Expert,

Requesting your suggestion for my subjected Query!

I have below listed Crosstab Queries: -

1. DomesticOrder_Crosstab
2. Forecast_Crosstab
3. ExportOrder_Crosstab
4. IntercoOrder_Crosstab

All above Crosstab Queries are having Product Code in Rows, Demand Month in Column and Demand Quantity in Value Field Respectively.

Apart from above I have one more table and Query as below

Table: -
1. Product Code Lists (Product Code is Primary Key)

Query: -
1. Month Code (MMM-YY Format)

Expected Result (Query) :-

Support which I am looking here is, I would like to create a consolidated demand (Demand Quantity) by product (In Row), by month (As Column Heading) considering all above Queries and Table

Product Code Lists (Table) to be considered as Row Value, Month Code (Query) to be considered as Column Heading

With Respect to above Rows & Column as reference, I would want a SUM IIF of above four crosstab Query Values

Looking forward your value input for the same!



Thanks & Regards,
Rajeshkumar R

[ranman256]

if you pick a month code, how do you get a crosstab with only 1 code and 1 month?

[ArviLaanemets]

In case you want to sum DomesticOrder, Forecast, ExportOrder and IntercoOrder for same product in same month, try:

SELECT ProductCode, SUM(Month1) AS [Month1], SUM(Month2) AS [Month2], ... FROM
(SELECT dom.ProductCode, "Domestic Order" AS [OrderType], dom.Month1, dom.Month2, ... FROM DomesticOrder_Crosstab dom
UNION
SELECT fc.ProductCode, "Forecast" AS [OrderType], fc.Month1, fc.Month2, ... FROM Forecast_Crosstab fc
UNION
SELECT exp.ProductCode, "Export Order" AS [OrderType], exp.Month1, exp.Month2, ... FROM ExportOrder_Crosstab exp
UNION
SELECT int.ProductCode, "Interco Order" AS [OrderType], int.Month1, int.Month2, ... FROM IntercoOrder_Crosstab int)
GROUP BY ProductCode

Rem. In case you have in some MonthN columns Null values, use Nz(MonthN,0) in SUM() parts.