Crosstab queries, <> and null values

[Edwinah63]

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

 

[XPS35]

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;

 

[Edwinah63]

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.

 

[John Spencer]

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