Pivot table count only

[MechEngSk]

Hi,

I have a database that collects downtime event information. I have several querries that calcualte the downtime and availability. These are then combined into one querry using union querry. In the union querry I use the NZ function to replace any null values for 100. I end up getting a short set of data for 7 day moving report:

Expr1000	Day1
39.58	23/01/2013
83.33	22/01/2013
100	21/01/2013
100	20/01/2013
58.33	19/01/2013
100	18/01/2013

I need to display this data in a line chart. I'm using pivot table with dates at the bottom and values on the vertical axis. However, the only option that comes up for the calculation in the pivot chart is Count. All the other ones are grayed out. I need to use the Sum.

I have tried to use another querry to collect the data and put it in a table, but the pivot chart behaved the same as through the querry.. I tired to remove the NZ statements form my union querry, which worked but gave undesired result, i.e. if there are not entries for a particular day, I need to have value of 100 not null. No entries means 100% availablity.



Here is my union querry text.

Select NZ([Day1Av],100), [Day1]
From [Daily Availability for Daily report 1 - Crush]
UNION ALL SELECT NZ([Day2Av],100), [Day2]
From [Daily Availability for Daily report 2 - Crush]
UNION ALL SELECT NZ([Day3Av],100), [Day3]
From [Daily Availability for Daily report 3 - Crush]
UNION ALL SELECT NZ([Day4Av],100), [Day4]
From [Daily Availability for Daily report 4 - Crush]
UNION ALL SELECT NZ([Day5Av],100), [Day5]
From [Daily Availability for Daily report 5 - Crush]
UNION ALL SELECT NZ([Day6Av],100), [Day6]
From [Daily Availability for Daily report 6 - Crush]
UNION ALL SELECT NZ([Day7Av],100), [Day7]
From [Daily Availability for Daily report 7 - Crush];

Can someone please assit me and advice me on what am I doing wrong? Why is it the NZ function causes the pivot chart to remove the other calculation options? I've tried searching everywhere, but this seems to be a rather unique problem. I appreciate all help.

[June7]

I think Nz function actually results in a string value, even only numerals are in the result.

Need alias fieldname defined on the first line.

Try converting to number with Val():

SELECT Val(NZ([Day1Av],100)) As Avg, [Day1] FROM

[MechEngSk]

Thanks that has worked. I would not have thought of that.