Crosstab queries - populating blanks with zeros

[Bob]

I have a crosstab query with product line as my row heading and date as my
column heading and am tracking monthly sales. How can I populate the cells in
the crosstab query that have no values with zeros.

Thanks.

 

[Duane Hookom]

This would be a great opportunity to post your SQL view. Since you haven't, I
can generically suggest a value like
Val(Nz(Sum([Sales]),0))

 

[Bob]

Duane,
Here is the SQL view from my crosstab query. I would like to populate the
cells that have no values with zeros.

-- TRANSFORM Sum([qry_POS By PL Category].SumOfCOS) AS SumOfSumOfCOS
SELECT [qry_POS By PL Category].[Branch Number], [qry_POS By PL
Category].City, [qry_POS By PL Category].[DHR P/L Catagories]
FROM [qry_POS By PL Category]
WHERE ((([qry_POS By PL Category].[DHR P/L Catagories])="Other"))
GROUP BY [qry_POS By PL Category].[Branch Number], [qry_POS By PL
Category].City, [qry_POS By PL Category].[DHR P/L Catagories]
PIVOT Format([Month],"yyyy-mm");

Bob

This would be a great opportunity to post your SQL view. Since you haven't, I
can generically suggest a value like
Val(Nz(Sum([Sales]),0))
--
Duane Hookom
Microsoft Access MVP

I have a crosstab query with product line as my row heading and date as my
column heading and am tracking monthly sales. How can I populate the cells in
the crosstab query that have no values with zeros.

Thanks.

 

[John Spencer]

As Duane suggested

TRANSFORM Val(NZ(Sum([qry_POS By PL Category].SumOfCOS),0)) AS SumOfSumOfCOS
SELECT [qry_POS By PL Category].[Branch Number]
, [qry_POS By PL Category].City
, [qry_POS By PL Category].[DHR P/L Catagories]
FROM [qry_POS By PL Category]
WHERE ((([qry_POS By PL Category].[DHR P/L Catagories])="Other"))
GROUP BY [qry_POS By PL Category].[Branch Number]
, [qry_POS By PL Category].City
, [qry_POS By PL Category].[DHR P/L Catagories]
PIVOT Format([Month],"yyyy-mm");

In place of Val you could also use CCur (for Currency), CDbl (for numbers
with decimals), or CLng (for Integers).
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Duane,
Here is the SQL view from my crosstab query. I would like to populate the
cells that have no values with zeros.

-- TRANSFORM Sum([qry_POS By PL Category].SumOfCOS) AS SumOfSumOfCOS
SELECT [qry_POS By PL Category].[Branch Number], [qry_POS By PL
Category].City, [qry_POS By PL Category].[DHR P/L Catagories]
FROM [qry_POS By PL Category]
WHERE ((([qry_POS By PL Category].[DHR P/L Catagories])="Other"))
GROUP BY [qry_POS By PL Category].[Branch Number], [qry_POS By PL
Category].City, [qry_POS By PL Category].[DHR P/L Catagories]
PIVOT Format([Month],"yyyy-mm");

Bob

This would be a great opportunity to post your SQL view. Since you
haven't, I
can generically suggest a value like
Val(Nz(Sum([Sales]),0))
--
Duane Hookom
Microsoft Access MVP

I have a crosstab query with product line as my row heading and date as
my
column heading and am tracking monthly sales. How can I populate the
cells in
the crosstab query that have no values with zeros.

Thanks.