Crosstab not recognizing 0s in calculation

[jd]

I have a crosstab query that shows data for several parts over several weeks.
I'm trying to get the average of the data for each part. I have an Nz
function so that all null values show 0, but when i try to do any
calculations, it doesn't recognize the 0s. For example, if i have 9 - 0's
and 1 - 10, the average shows as 10. Any ideas?
Thanks!

 

[KARL DEWEY]

Post the SQL for the crosstab query.

 

[jd]

TRANSFORM Nz(Sum([M1A Trial].SumOfwod_qty_req),0) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];

Thank you,

 

[KARL DEWEY]

It works for me.
Test data --
Week Num SumOfwod_qty_req CCN
1 4 A
1 5 B
2 5 B
2 0 A
3 4 A
3 0 B
Results --
CCN Avg Of SumOfwod_qty_req 1 2 3
A 2.66666666666667 4 0 4
B 3.33333333333333 5 5 0

Test your Avg function in a totals query.

--
KARL DEWEY
Build a little - Test a little

TRANSFORM Nz(Sum([M1A Trial].SumOfwod_qty_req),0) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];

Thank you,

Post the SQL for the crosstab query.

 

[jd]

Still not working. I have 25+ columns of data (qty req), and even when I
count, it is only counting the non-zero numbers.

It works for me.
Test data --
Week Num SumOfwod_qty_req CCN
1 4 A
1 5 B
2 5 B
2 0 A
3 4 A
3 0 B
Results --
CCN Avg Of SumOfwod_qty_req 1 2 3
A 2.66666666666667 4 0 4
B 3.33333333333333 5 5 0

Test your Avg function in a totals query.

--
KARL DEWEY
Build a little - Test a little

TRANSFORM Nz(Sum([M1A Trial].SumOfwod_qty_req),0) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];

Thank you,

Post the SQL for the crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that shows data for several parts over several weeks.
I'm trying to get the average of the data for each part. I have an Nz
function so that all null values show 0, but when i try to do any
calculations, it doesn't recognize the 0s. For example, if i have 9 - 0's
and 1 - 10, the average shows as 10. Any ideas?
Thanks!

 

[jd]

I made a smaller sample of "test" data to try it again and this is what I'm
getting:

CCN Avg Of Qty Req 1 2 3
a 1.5 1 2 0
b 3.5 2 0 5
c 3 3 0 0
d 3 0 4 2
e 40.5 5 76 0
f 8 7 9 0
g 40.5 3 0 78
h 8 0 0 8
I 24 45 23 4
j 1 0 0 1

Still not working. I have 25+ columns of data (qty req), and even when I
count, it is only counting the non-zero numbers.

It works for me.
Test data --
Week Num SumOfwod_qty_req CCN
1 4 A
1 5 B
2 5 B
2 0 A
3 4 A
3 0 B
Results --
CCN Avg Of SumOfwod_qty_req 1 2 3
A 2.66666666666667 4 0 4
B 3.33333333333333 5 5 0

Test your Avg function in a totals query.

--
KARL DEWEY
Build a little - Test a little

TRANSFORM Nz(Sum([M1A Trial].SumOfwod_qty_req),0) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];

Thank you,


:

Post the SQL for the crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that shows data for several parts over several weeks.
I'm trying to get the average of the data for each part. I have an Nz
function so that all null values show 0, but when i try to do any
calculations, it doesn't recognize the 0s. For example, if i have 9 - 0's
and 1 - 10, the average shows as 10. Any ideas?
Thanks!

 

[KARL DEWEY]

I do not know if this will make a difference but try it with NZ inside of the
SUM.
TRANSFORM Sum(Nz([M1A Trial].SumOfwod_qty_req,0)) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];
--
KARL DEWEY
Build a little - Test a little

I made a smaller sample of "test" data to try it again and this is what I'm
getting:

CCN Avg Of Qty Req 1 2 3
a 1.5 1 2 0
b 3.5 2 0 5
c 3 3 0 0
d 3 0 4 2
e 40.5 5 76 0
f 8 7 9 0
g 40.5 3 0 78
h 8 0 0 8
I 24 45 23 4
j 1 0 0 1

Still not working. I have 25+ columns of data (qty req), and even when I
count, it is only counting the non-zero numbers.

It works for me.
Test data --
Week Num SumOfwod_qty_req CCN
1 4 A
1 5 B
2 5 B
2 0 A
3 4 A
3 0 B
Results --
CCN Avg Of SumOfwod_qty_req 1 2 3
A 2.66666666666667 4 0 4
B 3.33333333333333 5 5 0

Test your Avg function in a totals query.

--
KARL DEWEY
Build a little - Test a little


:

TRANSFORM Nz(Sum([M1A Trial].SumOfwod_qty_req),0) AS SumOfSumOfwod_qty_req
SELECT [M1A Trial].CCN, Avg([M1A Trial].SumOfwod_qty_req) AS [Avg Of
SumOfwod_qty_req]
FROM [M1A Trial]
GROUP BY [M1A Trial].CCN
PIVOT [M1A Trial].[Week Num];

Thank you,


:

Post the SQL for the crosstab query.
--
KARL DEWEY
Build a little - Test a little


:

I have a crosstab query that shows data for several parts over several weeks.
I'm trying to get the average of the data for each part. I have an Nz
function so that all null values show 0, but when i try to do any
calculations, it doesn't recognize the 0s. For example, if i have 9 - 0's
and 1 - 10, the average shows as 10. Any ideas?
Thanks!