force zero into null in value field of a crosstab query

[FredB]

I have data in an Access 2003 query that lists each month's sales for a year
for each customer. When I run a crosstab query on this query, if there are
no sales for a particular month for a customer a null is returned.
Can I somehow force a zero into this field in the crosstab query?
If not, what is the best way to get the source query to have a result of
zero if there are no sales in the table it is based upon?
Or, if not, how can I populate the resulting table (my next step is to
create a table from the crosstab query results) with zeros where there are
nulls (without doing a separate update query)?

Thanks for any and all help.

 

[Ofer]

Try and use the NZ function to replace null with 0
NZ(fieldName,0)

 

[FredB]

Thank you for responding but the NZ function does not work in the Crosstab
query either.

 

[John Spencer]

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data type
to be correct, since Access has the habit of turning the NZ into a text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

 

[FredB]

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB

 

[Ofer]

In the NZ you didnt specify the value you want to assign incase of a null
Nz(Value,0)

try this
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales],0)) AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data type
to be correct, since Access has the habit of turning the NZ into a text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

 

[FredB]

Hi Ofer,

I think I did. The Value is:
[qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales] and the alternate
value is "0".

Fred

In the NZ you didnt specify the value you want to assign incase of a null
Nz(Value,0)

try this
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales],0)) AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data type
to be correct, since Access has the habit of turning the NZ into a text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

Thank you for responding but the NZ function does not work in the Crosstab
query either.

:

Try and use the NZ function to replace null with 0
NZ(fieldName,0)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have data in an Access 2003 query that lists each month's sales for a
year
for each customer. When I run a crosstab query on this query, if there
are
no sales for a particular month for a customer a null is returned.
Can I somehow force a zero into this field in the crosstab query?
If not, what is the best way to get the source query to have a result
of
zero if there are no sales in the table it is based upon?
Or, if not, how can I populate the resulting table (my next step is to
create a table from the crosstab query results) with zeros where there
are
nulls (without doing a separate update query)?

Thanks for any and all help.

 

[John Spencer]

As I mentioned in my first post, the NZ needs to be around the SUM, not
within the sum AND I would force the data type. Access will often in this
type of query using the NZ function, return your number field value as a
string. Leave out the CCUR and check your column display when you run the
query - if it is left-aligned you have a string of number characters; if it
is right-aligned you have a number. If it is left-aligned, you may want to
add the CCur function back. You can also use Cdbl or even CLng (if your
number doesn't have any fractional part).

TRANSFORM
CCUR(NZ(Sum([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales]),0))
AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS
Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data
type
to be correct, since Access has the habit of turning the NZ into a text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

 

[FredB]

Thank you John for taking the time, however, my original point was that I
want individual months that might be Null to become Zero, not the Sum because
if there is one Null in the Sum, the Sum will be Null also.

Thanks again.

FredB

As I mentioned in my first post, the NZ needs to be around the SUM, not
within the sum AND I would force the data type. Access will often in this
type of query using the NZ function, return your number field value as a
string. Leave out the CCUR and check your column display when you run the
query - if it is left-aligned you have a string of number characters; if it
is right-aligned you have a number. If it is left-aligned, you may want to
add the CCur function back. You can also use Cdbl or even CLng (if your
number doesn't have any fractional part).

TRANSFORM
CCUR(NZ(Sum([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales]),0))
AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS
Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data
type
to be correct, since Access has the habit of turning the NZ into a text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

Thank you for responding but the NZ function does not work in the
Crosstab
query either.

:

Try and use the NZ function to replace null with 0
NZ(fieldName,0)
--
If I answered your question, please mark it as an answer. That way, it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have data in an Access 2003 query that lists each month's sales
for a
year
for each customer. When I run a crosstab query on this query, if
there
are
no sales for a particular month for a customer a null is returned.
Can I somehow force a zero into this field in the crosstab query?
If not, what is the best way to get the source query to have a
result
of
zero if there are no sales in the table it is based upon?
Or, if not, how can I populate the resulting table (my next step is
to
create a table from the crosstab query results) with zeros where
there
are
nulls (without doing a separate update query)?

Thanks for any and all help.

 

[John Spencer]

SUM ignores NULLS in the fields it is summing. Avg, Min, Max, Count, all
ignore nulls.

For example,

Three records with the following values in FieldA
1
Null
5

Sum(FieldA) will be 6

Assuming rptPeriod is the Month, then this should give you zeroes in those
months where a customerid does not have any sales.

If you tried the query and it didn't give you the expected results, then
perhaps you can post a sample line of what you got versus what you wanted.

Good luck.

Thank you John for taking the time, however, my original point was that I
want individual months that might be Null to become Zero, not the Sum
because
if there is one Null in the Sum, the Sum will be Null also.

Thanks again.

FredB

As I mentioned in my first post, the NZ needs to be around the SUM, not
within the sum AND I would force the data type. Access will often in
this
type of query using the NZ function, return your number field value as a
string. Leave out the CCUR and check your column display when you run
the
query - if it is left-aligned you have a string of number characters; if
it
is right-aligned you have a number. If it is left-aligned, you may want
to
add the CCur function back. You can also use Cdbl or even CLng (if your
number doesn't have any fractional part).

TRANSFORM
CCUR(NZ(Sum([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales]),0))
AS Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Here is the SQL:
TRANSFORM
Sum(Nz([qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy].[Sales])) AS
Sales
SELECT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
FROM qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy
GROUP BY qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.customerId
PIVOT qryCW_Dtl_DistribSalesBgt_LyAllTyYtd_Access_TyLy.rptPeriod;

Thank you for looking at this.

FredB
:

Open your query in SQL mode.
Post the SQL TEXT here.

NZ should work, but you need to put it in the correct location and you
probably need to use one of the conversion functions to force the data
type
to be correct, since Access has the habit of turning the NZ into a
text
string vice keeping it as a number.

Something like
TRANSFORM CCur(Nz(Sum(TheField),0)) as SumOfField
SELECT ...
FROM ...

Thank you for responding but the NZ function does not work in the
Crosstab
query either.

:

Try and use the NZ function to replace null with 0
NZ(fieldName,0)
--
If I answered your question, please mark it as an answer. That way,
it
will
stay saved for a longer time, so other can benifit from it.

Good luck



:

I have data in an Access 2003 query that lists each month's sales
for a
year
for each customer. When I run a crosstab query on this query, if
there
are
no sales for a particular month for a customer a null is
returned.
Can I somehow force a zero into this field in the crosstab query?
If not, what is the best way to get the source query to have a
result
of
zero if there are no sales in the table it is based upon?
Or, if not, how can I populate the resulting table (my next step
is
to
create a table from the crosstab query results) with zeros where
there
are
nulls (without doing a separate update query)?

Thanks for any and all help.