A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Crosstab queries, and null values



 
 
Thread Tools Display Modes
  #1  
Old May 21st, 2010, 07:13 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default 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
Ads
  #2  
Old May 21st, 2010, 09:11 AM posted to microsoft.public.access.queries
xps35
external usenet poster
 
Posts: 22
Default 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  
Old May 21st, 2010, 11:29 AM posted to microsoft.public.access.queries
Edwinah63
external usenet poster
 
Posts: 16
Default 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  
Old May 21st, 2010, 02:28 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:46 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.