Crosstab-force rows

[Guest]

I have a crosstab set up but when the criteria doesnt match, it doesnt pull
in any data which is correct. However, i would like to show zeroes instead
of blanks. How can I force the zeroes to show in the results?

I tried doing if statements in all the fields that if it = "", then put 0,
but that didnt work.

thanks!

 

[Phil Smith]

For instance, you want sales of grapes, apples and oranges.

Grapes 500
Apples 400
Oranges 300

but last week, you sold no apples. You get

Grapes 500
Oranges 300

You WANT
Grapes 500
Apples 0
Oranges 300

When I have done it, I have created a dummy record for each fruit, with
a bizzare date, like 1/1/2095, and a count of zero.

Then in addition to all of my criteria, (pulled from a select form or
some such, on a seperate Criteria line I put that date.

This guarentees the query will find the dummy record, giving you your
row even if there were no sales that week, and the zero does not
otherwise efect your sums. You have to make sure that your Dummy
selector, like the bizzzare date, is not part of a group by.

Ugly, but it works. Maybe someone will have something prettier.

 

[John Spencer]

Could you post the SQL text (Menu View:SQL) of your query?

As a guess, you need to change the your first line in the SQL to read
something like

TRANSFORM CDbl(NZ(Sum([SomeField]),0)) as SumOfSomeField
SELECT ...

The NZ function will change Null(Blank) to Zero and CDbl will change the
result back to a number. Crosstab queries tend to change the value to a
string when using the NZ function, so I usually force the value back to a
number type using one of the conversion functions.

In the design view (query grid), you would edit the Value column
Field: SumOfSomeField: CDbl(NZ(Sum([TableName].[SomeField]),0))
Table: <<Blank>>
Total: Expression
CrossTab: Value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi John,

I have a similar problem, except it's with columns not rows. My column
heading is PaidMonth and the data starts with 200301, goes up to the present,
and with each new month there will be a new column. There are instances
however, where there will be no data for an entire month, and when this
happens then the column heading for that month does not appear in the
crosstab. Any suggestions on how I can get every month heading to show even
if there is no data for that month?

Thanks in advance!

Could you post the SQL text (Menu View:SQL) of your query?

As a guess, you need to change the your first line in the SQL to read
something like

TRANSFORM CDbl(NZ(Sum([SomeField]),0)) as SumOfSomeField
SELECT ...

The NZ function will change Null(Blank) to Zero and CDbl will change the
result back to a number. Crosstab queries tend to change the value to a
string when using the NZ function, so I usually force the value back to a
number type using one of the conversion functions.

In the design view (query grid), you would edit the Value column
Field: SumOfSomeField: CDbl(NZ(Sum([TableName].[SomeField]),0))
Table: <<Blank>>
Total: Expression
CrossTab: Value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a crosstab set up but when the criteria doesnt match, it doesnt pull
in any data which is correct. However, i would like to show zeroes
instead
of blanks. How can I force the zeroes to show in the results?

I tried doing if statements in all the fields that if it = "", then put 0,
but that didnt work.

thanks!

 

[John Spencer]

Suggest you post this as a new thread. Perhaps Duane Hookom will have a
suggestion for you.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi John,

I have a similar problem, except it's with columns not rows. My column
heading is PaidMonth and the data starts with 200301, goes up to the
present,
and with each new month there will be a new column. There are instances
however, where there will be no data for an entire month, and when this
happens then the column heading for that month does not appear in the
crosstab. Any suggestions on how I can get every month heading to show
even
if there is no data for that month?

Thanks in advance!

Could you post the SQL text (Menu View:SQL) of your query?

As a guess, you need to change the your first line in the SQL to read
something like

TRANSFORM CDbl(NZ(Sum([SomeField]),0)) as SumOfSomeField
SELECT ...

The NZ function will change Null(Blank) to Zero and CDbl will change the
result back to a number. Crosstab queries tend to change the value to a
string when using the NZ function, so I usually force the value back to a
number type using one of the conversion functions.

In the design view (query grid), you would edit the Value column
Field: SumOfSomeField: CDbl(NZ(Sum([TableName].[SomeField]),0))
Table: <<Blank>>
Total: Expression
CrossTab: Value

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a crosstab set up but when the criteria doesnt match, it doesnt
pull
in any data which is correct. However, i would like to show zeroes
instead
of blanks. How can I force the zeroes to show in the results?

I tried doing if statements in all the fields that if it = "", then put
0,
but that didnt work.

thanks!