Need zeros for null values in crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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...
 
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...
 
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...
 
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...
 
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...
 
Back
Top