Fill Blanks with Zero on CrossTab

[dtoney]

Excel has this trick that allows you to do a special edit where you can fill
the blank cells with zeros... which enables you to graph the zero result on a
chart. Is there a way to force a zero on a crosstab query?

I need to show data for all weeks... even when the value is zero on my
crosstab.

thanks!

 

[Duane Hookom]

This is generally done by wrapping your Value expression in Val(Nz(...... , 0))

If you share your SQL view, someone could provide a more appropriate value.

 

[Piet Linden]

Excel has this trick that allows you to do a special edit where you can fill
the blank cells with zeros... which enables you to graph the zero result on a
chart. Is there a way to force a zero on a crosstab query?

I need to show data for all weeks... even when the value is zero on my
crosstab.

thanks!

Allen Browne has lots of examples of having fun with crosstabs on his
website
www.allenbrowne.com

 

[dtoney]

Here is the SQL for my crosstab:

TRANSFORM Count(qry_OPSview.KEY_ID) AS CountOfKEY_ID
SELECT qry_OPSview.AREA AS AREA, Count(qry_OPSview.KEY_ID) AS [Total Of
KEY_ID]
FROM qry_OPSview
GROUP BY qry_OPSview.AREA
PIVOT Format([WEEK],"Short Date");

Here is the SQL from my select:
SELECT allpmdata.DATE_RECVD, allpmdata.DATE_CLOSED, allpmdata.TIME_RECVD,
allpmdata.INQ_STATUS_TIME, allpmdata.KEY_ID, allpmdata.ALT_KEY,
allpmdata.SEV, allpmdata.STAT, allpmdata.DESCRIPTION, allpmdata.TYPE,
allpmdata.AREA, allpmdata.INQ_CUST_ID, allpmdata.WEEK, allpmdata.INQ_EMP_ID_NO
FROM allpmdata
WHERE (((allpmdata.INQ_CUST_ID) Like "PROA*" Or (allpmdata.INQ_CUST_ID) Like
"O/*" Or (allpmdata.INQ_CUST_ID) Like "E/*" Or (allpmdata.INQ_CUST_ID) Like
"L/*"));

 

[Duane Hookom]

Try change the crosstab SQL to:

TRANSFORM Val(Nz(Count(qry_OPSview.KEY_ID),0)) AS CountOfKEY_ID
SELECT qry_OPSview.AREA AS AREA, Count(qry_OPSview.KEY_ID) AS [Total Of
KEY_ID]
FROM qry_OPSview
GROUP BY qry_OPSview.AREA
PIVOT Format([WEEK],"Short Date");

--
Duane Hookom
Microsoft Access MVP

Here is the SQL for my crosstab:

TRANSFORM Count(qry_OPSview.KEY_ID) AS CountOfKEY_ID
SELECT qry_OPSview.AREA AS AREA, Count(qry_OPSview.KEY_ID) AS [Total Of
KEY_ID]
FROM qry_OPSview
GROUP BY qry_OPSview.AREA
PIVOT Format([WEEK],"Short Date");

Here is the SQL from my select:
SELECT allpmdata.DATE_RECVD, allpmdata.DATE_CLOSED, allpmdata.TIME_RECVD,
allpmdata.INQ_STATUS_TIME, allpmdata.KEY_ID, allpmdata.ALT_KEY,
allpmdata.SEV, allpmdata.STAT, allpmdata.DESCRIPTION, allpmdata.TYPE,
allpmdata.AREA, allpmdata.INQ_CUST_ID, allpmdata.WEEK, allpmdata.INQ_EMP_ID_NO
FROM allpmdata
WHERE (((allpmdata.INQ_CUST_ID) Like "PROA*" Or (allpmdata.INQ_CUST_ID) Like
"O/*" Or (allpmdata.INQ_CUST_ID) Like "E/*" Or (allpmdata.INQ_CUST_ID) Like
"L/*"));

Excel has this trick that allows you to do a special edit where you can fill
the blank cells with zeros... which enables you to graph the zero result on a
chart. Is there a way to force a zero on a crosstab query?

I need to show data for all weeks... even when the value is zero on my
crosstab.

thanks!