'Zero' values in crosstab query...how to display?
Rob Falconer
query/count of value answer table, rather than 'blank fields' when the
count=0?
TIA
--
Rob Falconer
mailto:R...@rfalconr.demon.co.uk
icq:637615
Charlotte Foust
because there are no records in the underlying recordset that match up to
that Row and Column heading, so the count isn't really zero. About the only
way I can think of to fake it, it to use a maketable query based on the
crosstab query to create a table containing the information form the
crosstab, then using an update query to undate all the null fields to zero.
Charlotte Foust
cfo...@technologist.com
Rob Falconer wrote in message ...
Rob Falconer
<cfo...@worldnet.att.net> writes
I thought there *ought* to be a way, because the blank fields aren't
really nulls.....the count *is* zero. Ah well, Ill try your
suggestion.Thanks again.
Clive Bolton
questions.
If you are using Access 97, then use as the field in the query builder
myfld1: nz([myfld],0)
Access 2.0 has a description in Help for creating a function called
NullToZero() which will do something similar.
If you are using the query for a report, do not add zero to null values in
the query. Instead, format the textbox controls on the report layout to show
what you want to format values in this order - +ve;-ve;<zero>;<null>
#;#;0;0
I frequently use #;#;/.;/. This places a decimal point where no significant
value is shown, but provides the eye with a lining-up mechanism.
Clive Bolton
Tauranga, New Zealand
(Emails not welcome - please respond via this newsgroup)
Rob Falconer wrote in message ...
>In article <6per66$o...@bgtnsc02.worldnet.att.net>, Charlotte Foust
><cfo...@worldnet.att.net> writes
>>Sorry, but it can't be done
>>
André Steenveld
Say you have a table with the following layout and records.
rows columns values
aaa 111 1
bbb 222 2
ccc 333 3
rows text
columns text
values numerical
and your looking for a cross table with field 'rows' for the rows and
field 'columns' for the columns. The value is the sum over the field
'values' and a cross table would look like:
rows 111 222 333
aaa 1
bbb 2
ccc 3
TRANSFORM Sum(Tabel1.values) AS SomVanvalues
SELECT Tabel1.rows
FROM Tabel1
GROUP BY Tabel1.rows
PIVOT Tabel1.columns;
First try (with known values in the columns in advance.)
Now asume that you know all possible values that can be contained in
columns. In this example it are the values '111', '222' and '333'. For a
sumquery over your table and check per column for the value your interested
in. If the column value match the intended label, assign it the correct
value, otherwise assign zero. This will do nice for summing, other
statistic functions can be performed the same way (such as counting, min or
max) but not all. Now the results look like:
rows 111 222 333
aaa 1 0 0
bbb 0 2 0
ccc 0 0 3
SELECT Tabel1.rows, Sum(IIf([columns]="111",[values],0)) AS 111,
Sum(IIf([columns]="222",[values],0)) AS 222,
Sum(IIf([columns]="333",[values],0)) AS 333
FROM Tabel1
GROUP BY Tabel1.rows;
A little nice trick that I use specially when I want to base another query
on this table. The new query wil not execute this one each time when you
want to do designwork on it! You pay of cause in performance points.
Second try (with unknown values in the columns in advance.)
Say you do not know all possible values of the columns. The idea is to
generate a carthesian product of all the value combinations you are
interested in. (This can cost you performance point by the thousands!)
rows columns
aaa 111
bbb 111
ccc 111
aaa 222
bbb 222
ccc 222
aaa 333
bbb 333
ccc 333
Query: Cartesian product { rows X columns }
SELECT Tabel1.rows, Tabel1_1.columns
FROM Tabel1, Tabel1 AS Tabel1_1;
Now base a new cross table query on this one and your original table and
basicly apply the same trick in testing the values you want to calculate.
rows 111 222 333
aaa 1 0 0
bbb 0 2 0
ccc 0 0 3
TRANSFORM Sum(IIf(IsNull([values]),0,[values])) AS Expr1
SELECT [Cartesian product { rows X columns }].rows
FROM [Cartesian product { rows X columns }] LEFT JOIN Tabel1 ON ([Cartesian
product { rows X columns }].columns = Tabel1.columns) AND ([Cartesian
product { rows X columns }].rows = Tabel1.rows)
GROUP BY [Cartesian product { rows X columns }].rows
PIVOT [Cartesian product { rows X columns }].columns;
I don't know which is the best. Generating a table works and it may be that
you pay the least performance points, but it takes some time to code. The
first try above works fine for me but is not always as flexible as you want
it. The second try always works... but can be slow. And maybe, with al
little fantasy, it is possible to come up with a fast and always working
solution.
André Steenveld.
> Charlotte Foust <cfo...@worldnet.att.net> wrote in artikel
<6per66$o...@bgtnsc02.worldnet.att.net>...
> Sorry, but it can't be done. The problem is that you're seeing blank
fields
> because there are no records in the underlying recordset that match up to
> that Row and Column heading, so the count isn't really zero. About the
only
> way I can think of to fake it, it to use a maketable query based on the
> crosstab query to create a table containing the information form the
> crosstab, then using an update query to undate all the null fields to
zero.
>
> Charlotte Foust
> cfo...@technologist.com
>
>
André Steenveld
Clive Bolton <al...@wave.co.nz> schreef in artikel
<6pg4op$55g$1...@news.wave.co.nz>...
> Rob, you should specify the Access version you are using, when asking
> questions.
>
> If you are using Access 97, then use as the field in the query builder
>
> myfld1: nz([myfld],0)
>
> Access 2.0 has a description in Help for creating a function called
> NullToZero() which will do something similar.
>
Hmmm... that fast and always working solution does exist! Thanks Clive!
André Steenveld.
Rob Falconer
<al...@wave.co.nz> writes
>Rob, you should specify the Access version you are using, when asking
>questions.
>
>If you are using Access 97, then use as the field in the query builder
>
>myfld1: nz([myfld],0)
>
>Access 2.0 has a description in Help for creating a function called
>NullToZero() which will do something similar.
>
>the query. Instead, format the textbox controls on the report layout to show
>what you want to format values in this order - +ve;-ve;<zero>;<null>
>#;#;0;0
>
>I frequently use #;#;/.;/. This places a decimal point where no significant
>value is shown, but provides the eye with a lining-up mechanism.
Wonderfully simple, #;#;0;0; works a treat.
Thanks a million :-)
(and thank you to everyone else who responded)
>>>Rob Falconer wrote in message ...
>>>>Could someone please tell me how to get zeros displayed in a crosstab
>>>>query/count of value answer table, rather than 'blank fields' when the
>>>>count=0?