Prev: Crosstab queries, <> and null values
Next: Total - Total Fields in a Totals Query
From: XPS35 on 21 May 2010 04:11
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

From: Edwinah63 on 21 May 2010 06:29
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.
From: John Spencer on 21 May 2010 09:28
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.
 | 
Pages: 1
Prev: Crosstab queries, <> and null values
Next: Total - Total Fields in a Totals Query