Hi all! Have a look: [URL=htt

[dacllog]

Hi all!

Have a look:



My problem is this: Cargonumber 070003, There is also a month_of_creation from 01-04-2007 but since theres no order_qty in 01-04-2007 Access ignores it..

My SQL Looks like this:
TRANSFORM Max([Alle Forecasts].Forecast) AS MaksOfForecast
SELECT [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, Actual_Orders.Order_Qty, Actual_Orders.Order_Month, Sum([MaksOfForecast]) AS [FC Consolidated], [FC Consolidated]-[Order_Qty] AS Diff
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) AND ((Actual_Orders.Order_Month)=[Month_of_Creation]))
GROUP BY [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, Actual_Orders.Order_Qty, Actual_Orders.Order_Month
PIVOT [Alle Forecasts].Month_of_ordering;

I would like to get a zero in 'Order_Qty' where there is a Month_of_Creation but no Order_Qty.

Thanks!

Daniel

 

[maxhugen]

Try using the function Nz() in the query, eg: Nz(Actual_Orders.Order_Qty,0)

Max Hugen
Australia

 

[dacllog]

Hi Max,

Now I've tried your Sugestion - can't get it to work.

We're should I put the Nz-function in my Sql?

 

[maxhugen]

[tt]TRANSFORM Max([Alle Forecasts].Forecast) AS MaksOfForecast
SELECT [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, [red]Nz([/red]Actual_Orders.Order_Qty[red], o)[/red], Actual_Orders.Order_Month, Sum([MaksOfForecast]) AS [FC Consolidated], [FC Consolidated]-[Order_Qty] AS Diff
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) AND ((Actual_Orders.Order_Month)=[Month_of_Creation]))
GROUP BY [Alle Forecasts].HC_cargoNumber, [Alle Forecasts].Month_of_Creation, Actual_Orders.Order_Qty, Actual_Orders.Order_Month
PIVOT [Alle Forecasts].Month_of_ordering;

Max Hugen
Australia

 

[dhookom]

I would create the crosstab using the method found at faq703-5466.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]