Cross tab query

[rowfei]

I have a cross tab query with the codes below:

TRANSFORM Sum(OrdersDetail.Quantity) AS SumOfQuantity
SELECT OrdersDetail.[Product Name]
FROM OrdersDetail
GROUP BY OrdersDetail.[Product Name]
PIVOT OrdersDetail.Sold;

Since some product never been sold, that's why it shows blank. What codes need to be added in order to display those blank field to "0."

Thanks,

 

[bob fitz]

Not at all sure it will work, but you could try this:

TRANSFORM Sum([COLOR=red]Nz([/COLOR]OrdersDetail.Quantity[COLOR=red],"0")[/COLOR][COLOR=black])[/COLOR] AS SumOfQuantity
SELECT OrdersDetail.[Product Name]
FROM OrdersDetail
GROUP BY OrdersDetail.[Product Name]
PIVOT OrdersDetail.Sold;

 

[rowfei]

Just tried, still shows the blank field instead of 0. Any more suggestion?

 

[bob fitz]

TRANSFORM Nz(Sum(OrdersDetail.Quantity),"0") AS SumOfQuantity
SELECT OrdersDetail.[Product Name]
FROM OrdersDetail
GROUP BY OrdersDetail.[Product Name]
PIVOT OrdersDetail.Sold;

 

[rowfei]

It works now. Thank you so much!!!!

 

[bob fitz]

Good. Glad to help.