If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Crosstab queries, and null values
Hi everyone,
I have a crosstab query attached to a graph on a report. It shows quantity of widgets produced by each employee for a specified period. The client wants all months shown regardless of zero widgets. If I just use the Access graph wizard, it only shows the months when there are some values not all months with or without zero values. The final graph is supposed to be sum(widgets) and each bar represents a worker. So... MonthSt WorkerName #Widgets 01/Jan/2010 Bob 10 01/Jan/2010 Dave 5 01/feb/2010 [null] 0 01/Mar/2010 Bob 16 01/Apr/2010 [null] 0 01/Jul/2010 Bob 1 01/Jul/2010 Dave 7 etc Creating the crosstab based on the above data via the wizard is Ok, but where the workername is null, it displays "" on the crosstab query and consequently with its own coloured box on the legend even tho' there is no data for it and the bar is not shown on the graph. Can I remove it? Or mask it or just suppress those values? Thanks in advance Edwinah63 |
#2
|
|||
|
|||
Crosstab queries, <> and null values
Edwinah63 wrote:
Hi everyone, I have a crosstab query attached to a graph on a report. It shows quantity of widgets produced by each employee for a specified period. The client wants all months shown regardless of zero widgets. If I just use the Access graph wizard, it only shows the months when there are some values not all months with or without zero values. The final graph is supposed to be sum(widgets) and each bar represents a worker. So... MonthSt WorkerName #Widgets 01/Jan/2010 Bob 10 01/Jan/2010 Dave 5 01/feb/2010 [null] 0 01/Mar/2010 Bob 16 01/Apr/2010 [null] 0 01/Jul/2010 Bob 1 01/Jul/2010 Dave 7 etc Creating the crosstab based on the above data via the wizard is Ok, but where the workername is null, it displays "" on the crosstab query and consequently with its own coloured box on the legend even tho' there is no data for it and the bar is not shown on the graph. Can I remove it? Or mask it or just suppress those values? Thanks in advance Edwinah63 You can add criteria to the crosstab query to eliminate data. The query then could look like: TRANSFORM Sum([#Widgets]) AS [Widgets] SELECT MonthSt FROM YourData WHERE WorkerName Is Not Null GROUP BY MonthSt PIVOT WorkerName; -- Groeten, Peter http://access.xps350.com |
#3
|
|||
|
|||
Crosstab queries, <> and null values
Hi Peter,
I have already tried this, but the client wants all months shown regardless of zero widgets so if I apply workername is null I lose the zero value stuff the client wants to see. Thanks for your reply. |
#4
|
|||
|
|||
Crosstab queries, <> and null values
Try using the NZ function to force a name.
TRANSFORM Sum([#Widgets]) AS [Widgets] SELECT MonthSt FROM YourData GROUP BY MonthSt PIVOT Nz(WorkerName,"No Activity"); John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Edwinah63 wrote: Hi Peter, I have already tried this, but the client wants all months shown regardless of zero widgets so if I apply workername is null I lose the zero value stuff the client wants to see. Thanks for your reply. |
Thread Tools | |
Display Modes | |
|
|