Need zeros for null values in crosstab query

S

Susan L

Have searched the groups on the topic, found Nz, but can't get it to work. I
probably have the expression in the wrong place in my query. It's in the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
 
M

Michel Walsh

Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP
 
S

Susan L

thanks for your response. Since I had Nz after COUNT, I assumed you might
mean put Nz before COUNT? I tried the following in the query grid and got an
error "Expression has a function with wrong number of arguments." The Value:
Nz(Count(Val([TicketNum],0))) Any thoughts?
--
susan


Michel Walsh said:
Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP


Susan L said:
Have searched the groups on the topic, found Nz, but can't get it to work.
I
probably have the expression in the wrong place in my query. It's in the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application
Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
Click to expand...
Click to expand...
 
J

John Spencer

Transform CLng(NZ(Count([TicketNum]),0))

If you want to COUNT tickets.

If you want to SUM the number of tickets

TRANSFORM CLng(NZ(Sum([TicketNum]),0))

Breaking that down into steps
Count([TicketNum]) counts the fields where ticket Num has a value

Sum([TicketNum]) would add up the numbers in TicketNum (if it is a number
field)

Nz(Sum([TicketNum]),0) will return a zero when the Sum of all the
ticketNums is null (that is no values to Sum)

CLng will turn that into a number. In Crosstab queries when you use NZ,
Access will usually return a string. If you want to do anything with the
returned value then you need to force its type into one of the number types.
CLng, CDbl, Val are usually the best choices.


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

Susan L said:
thanks for your response. Since I had Nz after COUNT, I assumed you might
mean put Nz before COUNT? I tried the following in the query grid and got
an
error "Expression has a function with wrong number of arguments." The
Value:
Nz(Count(Val([TicketNum],0))) Any thoughts?
--
susan


Michel Walsh said:
Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP


Susan L said:
Have searched the groups on the topic, found Nz, but can't get it to
work.
I
probably have the expression in the wrong place in my query. It's in
the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS
CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application
Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
S

Susan L

Wow, John! Thanks for the very clear explanation. The CLng/count version
works perfectly. I've filed your response in my Access Tips database for
future reference.

PS: I work not far from you - Manassas, VA.
--
susan


John Spencer said:
Transform CLng(NZ(Count([TicketNum]),0))

If you want to COUNT tickets.

If you want to SUM the number of tickets

TRANSFORM CLng(NZ(Sum([TicketNum]),0))

Breaking that down into steps
Count([TicketNum]) counts the fields where ticket Num has a value

Sum([TicketNum]) would add up the numbers in TicketNum (if it is a number
field)

Nz(Sum([TicketNum]),0) will return a zero when the Sum of all the
ticketNums is null (that is no values to Sum)

CLng will turn that into a number. In Crosstab queries when you use NZ,
Access will usually return a string. If you want to do anything with the
returned value then you need to force its type into one of the number types.
CLng, CDbl, Val are usually the best choices.


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

Susan L said:
thanks for your response. Since I had Nz after COUNT, I assumed you might
mean put Nz before COUNT? I tried the following in the query grid and got
an
error "Expression has a function with wrong number of arguments." The
Value:
Nz(Count(Val([TicketNum],0))) Any thoughts?
--
susan


Michel Walsh said:
Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP


Have searched the groups on the topic, found Nz, but can't get it to
work.
I
probably have the expression in the wrong place in my query. It's in
the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS
CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application
Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

You already have a working solution, but this one was not because the ,0
was at the wrong place, it should be the second argument of Nz, while it is
shows as second argument of val:

Val([TicketNum],0)


Oh, and when I said, 'after', I meant, 'executed after', so, to the left,
physically, in the code.


Vanderghast, Access MVP


Susan L said:
thanks for your response. Since I had Nz after COUNT, I assumed you might
mean put Nz before COUNT? I tried the following in the query grid and got
an
error "Expression has a function with wrong number of arguments." The
Value:
Nz(Count(Val([TicketNum],0))) Any thoughts?
--
susan


Michel Walsh said:
Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP


Susan L said:
Have searched the groups on the topic, found Nz, but can't get it to
work.
I
probably have the expression in the wrong place in my query. It's in
the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS
CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application
Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
S

Susan L

Michael: Thanks so much for the clarification.
--
susan


Michel Walsh said:
You already have a working solution, but this one was not because the ,0
was at the wrong place, it should be the second argument of Nz, while it is
shows as second argument of val:

Val([TicketNum],0)


Oh, and when I said, 'after', I meant, 'executed after', so, to the left,
physically, in the code.


Vanderghast, Access MVP


Susan L said:
thanks for your response. Since I had Nz after COUNT, I assumed you might
mean put Nz before COUNT? I tried the following in the query grid and got
an
error "Expression has a function with wrong number of arguments." The
Value:
Nz(Count(Val([TicketNum],0))) Any thoughts?
--
susan


Michel Walsh said:
Apply the NZ after the COUNT, not before:

TRANSFORM Nz(COUNT(...), 0)
SELECT ...


instead of the actual

TRANSFORM COUNT(Nz( ..., 0) )
SELECT ...




Hoping it may help,
Vanderghast, Access MVP


Have searched the groups on the topic, found Nz, but can't get it to
work.
I
probably have the expression in the wrong place in my query. It's in
the
Value column.

Here's the SQL for Nz in the Value column :
TRANSFORM Count(Val(Nz([TicketNum],0))) AS [The Value]
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT qry_Metrics_Crosstabs_Base.Category In ("Business
Guidance","Application Error","Exchange Agreements","State Interface
Change","State Interface Error","State Testing","Technical Guidance");

Here's the SQL for placement in the Column heading (called Catetgory).

TRANSFORM Count(qry_Metrics_Crosstabs_Base.TicketNum) AS
CountOfTicketNum
SELECT qry_Metrics_Crosstabs_Base.ProgramArea,
Count(qry_Metrics_Crosstabs_Base.TicketNum) AS [Total Tickets]
FROM qry_Metrics_Crosstabs_Base
WHERE (((qry_Metrics_Crosstabs_Base.ProgramArea)="CSENet"))
GROUP BY qry_Metrics_Crosstabs_Base.ProgramArea
PIVOT Nz([Category],0) In ("Business Guidance","Application
Error","Exchange
Agreements","State Interface Change","State Interface Error","State
Testing","Technical Guidance");

Would appreciate your help. Thanks.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab query for the report 2
change null count in crosstab qry to zeros Trouble with Nz 9
Missing Row Field in Crosstab Query 0
Missing Row Field in Crosstab Query 1
Crosstab Query - populating blanks with zeros 2
Totals in crosstab query 0
Crosstab Query - Column sorting problem - Ascending 3
how to replace null value with zero in crosstab query 6

Top