How do i replace empty cells with zeros after a cross tab query

[Guest]

I have a whole bunch of fish survey data, where i have entered a count for
each family present on each day. I want to do a cross tab query with the
families across the top (column heading) and day down the side (row heading)
with the count for each family on each cell (value). this is easy. What i
want to know is if it is possible to get access to fill all of the empty
cells in the resulting query with zeros (theoretically each family had a
chance of being counted each day - there just were not any, so their count is
zero). This will save adding in a row for every single family that may be
counted and giving it a count of zero, which will make the database
uneccessarily huge.
Can anyone help with this?

 

[Allen Browne]

Switch the query to SQL view (view menu), and add Nz() around the expression
in the TRANSFORM clause.

You will end up with something like this:
TRANSFORM Nz(Sum(Orders.QtySold),0) AS SumOfQtySold
SELECT ...

 

[Guest]

Thanks Allen, your suggestion worked fine and was very helpful.
However, something very strange is happening in my database since i did
this...-subsequent queries do not seem to recognize the zeros as zeros. If i
try and do a subsequent query to count the number of days a particualr family
was observed, by setting familiyX >0 as a criteria, the query returns all
sampling dates. If i set familyX = 0, the query returns no sampling days. It
seems to think that 0 is some how >0 and does not = 0. I copied the
crosstab query into excel, re-copied it within excel pasting only the values
(to try and get rid of any funky things) and then imported it back into
access and the same things still happend. Very strange. The same query used
to work on a table that otherwise appears to be exactly the same. Has anyone
ever come across something like this?

 

[Allen Browne]

Try typecasting the expression, to help JET will recognise it as a number.

If it's a whole number, use:
TRANSFORM CLng(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold
Or for a fractional number:
TRANSFORM CDbl(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold

For more info, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Nick]

I am using the format to get "0" in my crosstab. but I am getting the
required result. I am sending SQL. This catagore does not have an entery but
it is a required on my report.
TRANSFORM cdbl( NZ(count(licensetcount.AcctionId),0)) AS CountOfAcctionId
SELECT licensetcount.PosDlicense, Count(licensetcount.AcctionId) AS [Total
Of AcctionId]

Thanks
FROM licensetcount
GROUP BY licensetcount.PosDlicense
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

 

[Jeff Boyce]

Look at the Nz() function to generate a "0".

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am using the format to get "0" in my crosstab. but I am getting the
required result. I am sending SQL. This catagore does not have an entery
but
it is a required on my report.
TRANSFORM cdbl( NZ(count(licensetcount.AcctionId),0)) AS CountOfAcctionId
SELECT licensetcount.PosDlicense, Count(licensetcount.AcctionId) AS [Total
Of AcctionId]

Thanks
FROM licensetcount
GROUP BY licensetcount.PosDlicense
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Try typecasting the expression, to help JET will recognise it as a
number.

If it's a whole number, use:
TRANSFORM CLng(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold
Or for a fractional number:
TRANSFORM CDbl(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold

For more info, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html

 

[Douglas J. Steele]

Are you saying that you're trying to get zeroes for an entire row where
there aren't any values for the particular value of PosDlicense? If no rows
exist with the particular value, there's no way to generate zeroes.

You'd need to join table licensetcount with a table of all valid PosDLicense
values (using a Right Join to that all PosDLicense entries are represented),
and base your cross-tab query on that query.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I am using the format to get "0" in my crosstab. but I am getting the
required result. I am sending SQL. This catagore does not have an entery
but
it is a required on my report.
TRANSFORM cdbl( NZ(count(licensetcount.AcctionId),0)) AS CountOfAcctionId
SELECT licensetcount.PosDlicense, Count(licensetcount.AcctionId) AS [Total
Of AcctionId]

Thanks
FROM licensetcount
GROUP BY licensetcount.PosDlicense
PIVOT Format([ActionDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Try typecasting the expression, to help JET will recognise it as a
number.

If it's a whole number, use:
TRANSFORM CLng(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold
Or for a fractional number:
TRANSFORM CDbl(Nz(Sum(Orders.QtySold),0)) AS SumOfQtySold

For more info, see:
Calculated fields misinterpreted
at:
http://members.iinet.net.au/~allenbrowne/ser-45.html