Crosstab queries, <> and null values


E

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
 
Ad

Advertisements

X

XPS35

Edwinah63 said:
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;
 
E

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.
 
Ad

Advertisements

J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top