adding different columns in a cross tab report

[nms]

Good evening and any help will be greatly appreciated!!

I have a cross tab report in which I need to add columns 1, 2, and 3
together and then 4 and 5 together in separate calculated fields to create a
percentage (which works).

This used to work until I added the following to convert null values to
zeros (I need the zeros to calculate):

CountOfStatus: Nz(Count([dbo_HPD_HelpDesk].[Status]),0)

But when I ran the report after adding the Nz function to convert the null
values to 0, the addition stopped working. I tried changing the calculated
field in the text box and typed in many different ways to calculate this
now.

=([1]+[2]+[3]) which gives for the first row: 342 instead of 9
or
=sum([1]+[2]+[3]) which give the same number for each row

Priority CountOfStatus 1 2 3 4 5

0 174 3 4 2 2 163
1 767 5 2 0 12 748
2 356 3 1 1 13 338
3 40 0 0 0 2 38

This is the SQL call for the query

TRANSFORM Nz(Count(dbo_HPD_HelpDesk.Status),0) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between #12/1/2007# And
#12/31/2007#))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[Duane Hookom]

Use
CountOfStatus: Val(Nz(Count([dbo_HPD_HelpDesk].[Status]),0))

 

[nms]

Thanks Duane-worked like a charm!!!

Use
CountOfStatus: Val(Nz(Count([dbo_HPD_HelpDesk].[Status]),0))

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Good evening and any help will be greatly appreciated!!

I have a cross tab report in which I need to add columns 1, 2, and 3
together and then 4 and 5 together in separate calculated fields to create a
percentage (which works).

This used to work until I added the following to convert null values to
zeros (I need the zeros to calculate):

CountOfStatus: Nz(Count([dbo_HPD_HelpDesk].[Status]),0)

But when I ran the report after adding the Nz function to convert the null
values to 0, the addition stopped working. I tried changing the calculated
field in the text box and typed in many different ways to calculate this
now.

=([1]+[2]+[3]) which gives for the first row: 342 instead of 9
or
=sum([1]+[2]+[3]) which give the same number for each row

Priority CountOfStatus 1 2 3 4 5

0 174 3 4 2 2 163
1 767 5 2 0 12 748
2 356 3 1 1 13 338
3 40 0 0 0 2 38

This is the SQL call for the query

TRANSFORM Nz(Count(dbo_HPD_HelpDesk.Status),0) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between #12/1/2007# And
#12/31/2007#))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;

 

[Duane Hookom]

Glad it worked for you. I should have told you why.
Nz() returns a variant data type value. If you look at the datasheet view of
your crosstab, I would expect the Nz() values will be left-aligned. Wrapping
the Nz() inside Val() converts the variant to a numeric value.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Thanks Duane-worked like a charm!!!

Use
CountOfStatus: Val(Nz(Count([dbo_HPD_HelpDesk].[Status]),0))

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

Good evening and any help will be greatly appreciated!!

I have a cross tab report in which I need to add columns 1, 2, and 3
together and then 4 and 5 together in separate calculated fields to create a
percentage (which works).

This used to work until I added the following to convert null values to
zeros (I need the zeros to calculate):

CountOfStatus: Nz(Count([dbo_HPD_HelpDesk].[Status]),0)

But when I ran the report after adding the Nz function to convert the null
values to 0, the addition stopped working. I tried changing the calculated
field in the text box and typed in many different ways to calculate this
now.

=([1]+[2]+[3]) which gives for the first row: 342 instead of 9
or
=sum([1]+[2]+[3]) which give the same number for each row

Priority CountOfStatus 1 2 3 4 5

0 174 3 4 2 2 163
1 767 5 2 0 12 748
2 356 3 1 1 13 338
3 40 0 0 0 2 38

This is the SQL call for the query

TRANSFORM Nz(Count(dbo_HPD_HelpDesk.Status),0) AS CountOfStatus
SELECT dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent") AS PriorityName,
Count(dbo_HPD_HelpDesk.Status) AS CountOfStatus1
FROM dbo_HPD_HelpDesk
WHERE (((DateAdd("s",[arrival_time],#1/1/1970#)) Between #12/1/2007# And
#12/31/2007#))
GROUP BY dbo_HPD_HelpDesk.Priority, Switch([Priority] Like
"[0]*","Low",[priority] Like "[1]*","Medium",[Priority] Like
"[2]*","High",[Priority] Like "[3]*","Urgent")
PIVOT dbo_HPD_HelpDesk.Status;