SQL / Query Question, please

[dacllog]

I'm working on a forecasting follow-up tool.

I've got two tables:
1: All_Forecasts (Month_of_Creation, Item_number, Month_of_Ordering and Forecast).
2: Actual_Orders (Item_Number, Order_Month And Order_Qty).

My SQL untill now looks like this:

TRANSFORM Max([Alle Forecasts].Forecast) AS MaksOfForecast
SELECT [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, Sum([MaksOfForecast]) AS [FC Consolidated]
FROM ([Alle Forecasts] INNER JOIN Actual_Orders ON [Alle Forecasts].HC_cargoNumber = Actual_Orders.Varenummer) INNER JOIN TestTabel ON [Alle Forecasts].HC_cargoNumber = TestTabel.Varenummer
WHERE ((([Alle Forecasts].Month_of_ordering)<[Month_of_Creation]+65))
GROUP BY [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation
PIVOT [Alle Forecasts].Month_of_ordering;

It gives me the Consolidated forecast for each month. What I need now is to subtract the Order_Qty in each month.

I've tried to simply subtract it from my FC Consolidated, but that doesn't work at all...?

Any ideas?

Thanks!

 

[dacllog]

Should have been like this:

I'm working on a forecasting follow-up tool.

I've got two tables:
1: All_Forecasts (Month_of_Creation, HC_cargoNumber, Month_of_Ordering and Forecast).
2: Actual_Orders (Varenummer, Order_Month And Order_Qty).

My SQL untill now looks like this:

TRANSFORM Max([Alle Forecasts].Forecast) AS MaksOfForecast
SELECT [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, Sum([MaksOfForecast]) AS [FC Consolidated]
FROM ([Alle Forecasts] INNER JOIN Actual_Orders ON [Alle Forecasts].HC_cargoNumber = Actual_Orders.Varenummer) INNER JOIN TestTabel ON [Alle Forecasts].HC_cargoNumber = TestTabel.Varenummer
WHERE ((([Alle Forecasts].Month_of_ordering)<[Month_of_Creation]+65))
GROUP BY [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation
PIVOT [Alle Forecasts].Month_of_ordering;

It gives me the Consolidated forecast for each month. What I need now is to subtract the Order_Qty in each month.

I've tried to simply subtract it from my FC Consolidated, but that doesn't work at all...?

Any ideas?

Thanks!