Fill Blanks with Zero on CrossTab

  • Thread starter Thread starter dtoney
  • Start date Start date
D

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!
 
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.
 
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!
Click to expand...

Allen Browne has lots of examples of having fun with crosstabs on his
website
www.allenbrowne.com
 
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/*"));
 
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


dtoney said:
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/*"));




dtoney said:
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!
Click to expand...
Click to expand...
 
Back
Top