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

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

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.

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.