Help with Average

G

Guest

I got a very funny situation here.I've calculated SUM for the following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these date
fields have got numeric values.I then summed each field (i.e 200407) on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then selected the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one keeps
giving incorrect values.Yes I got zero values on some of my fields.Could that
be the reason??The funny thing is when i test the dynaset results on Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
 
J

John Spencer (MVP)

You may need to force the values being returned by NZ to be numeric. In some
circumstances NZ returns a text value of the number and the plus sign then acts
like a concatenation operator. So that 10+0+0+10+0 could return a string of
1000100. And then the average function manages to turn that string into a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))
 
G

Guest

It doesn't seem to be working
Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the
problem?
This Average thing is wrong



John Spencer (MVP) said:
You may need to force the values being returned by NZ to be numeric. In some
circumstances NZ returns a text value of the number and the plus sign then acts
like a concatenation operator. So that 10+0+0+10+0 could return a string of
1000100. And then the average function manages to turn that string into a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))
I got a very funny situation here.I've calculated SUM for the following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these date
fields have got numeric values.I then summed each field (i.e 200407) on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then selected the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one keeps
giving incorrect values.Yes I got zero values on some of my fields.Could that
be the reason??The funny thing is when i test the dynaset results on Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
Click to expand...
Click to expand...
 
D

Duane Hookom

What is your SQL view of your query?

--
Duane Hookom
MS Access MVP
--

Benton said:
It doesn't seem to be working
Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the
problem?
This Average thing is wrong



John Spencer (MVP) said:
You may need to force the values being returned by NZ to be numeric. In
some
circumstances NZ returns a text value of the number and the plus sign
then acts
like a concatenation operator. So that 10+0+0+10+0 could return a string
of
1000100. And then the average function manages to turn that string into
a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))
I got a very funny situation here.I've calculated SUM for the
following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these
date
fields have got numeric values.I then summed each field (i.e 200407)
on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then selected
the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one
keeps
giving incorrect values.Yes I got zero values on some of my
fields.Could that
be the reason??The funny thing is when i test the dynaset results on
Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Duane, sorry 4 taking long. here's my sql view

SELECT Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region,
Sum(Health_History.[monthly average 2001/2002]) AS [SumOfmonthly average
2001/2002], Sum(Health_History.[monthly average 2002/2003]) AS [SumOfmonthly
average 2002/2003], Sum(Health_Prod1.[200307]) AS SumOf200307,
Sum(Health_Prod1.[200308]) AS SumOf200308, Sum(Health_Prod1.[200309]) AS
SumOf200309, Sum(Health_Prod1.[200310]) AS SumOf200310,
Sum(Health_Prod1.[200311]) AS SumOf200311, Sum(Health_Prod1.[200312]) AS
SumOf200312, Sum(Health_Prod1.[200401]) AS SumOf200401,
Sum(Health_Prod1.[200402]) AS SumOf200402, Sum(Health_Prod1.[200403]) AS
SumOf200403, Sum(Health_Prod1.[200404]) AS SumOf200404,
Sum(Health_Prod1.[200405]) AS SumOf200405, Sum(Health_Prod1.[200406]) AS
SumOf200406, Sum(Health_Prod1.[200407]) AS SumOf200407,
Sum(Health_Prod1.[200408]) AS SumOf200408, Sum(Health_Prod1.[200409]) AS
SumOf200409, Sum(Health_Prod1.[200410]) AS SumOf200410,
Sum(Health_Prod1.[200411]) AS SumOf200411, Sum(Health_Prod1.[200412]) AS
SumOf200412, Sum(Health_Prod1.[200501]) AS SumOf200501,
Sum(Health_Prod1.[200502]) AS SumOf200502, Sum(Health_Prod1.[200503]) AS
SumOf200503, Sum(Health_Prod1.[200504]) AS SumOf200504,
Sum([Health_Prod1]![200407]+[Health_Prod1]![200408]+[Health_Prod1]![200409]+[Health_Prod1]![200410]+[Health_Prod1]![200411]+[Health_Prod1]![200412]+[Health_Prod1]![200501]+[Health_Prod1]![200502]+[Health_Prod1]![200503]+[Health_Prod1]![200504])
AS Expr111,
(Avg([200407])+(Avg([200408])+(Avg([200409])+(Avg([200410])+(Avg([200411])+(Avg([200412])+(Avg([200501])+(Avg([200502])+(Avg([200503])+(Avg([200504])))))))))))/10 AS [AVG]
FROM ((Health_Prod1 INNER JOIN Health_History ON Health_Prod1.Broker =
Health_History.[brk code]) INNER JOIN (Health_Linkages INNER JOIN Regions ON
Health_Linkages.PRIMARY_DIST_CHNNL = Regions.[Dis channel number]) ON
Health_Prod1.Broker = Health_Linkages.BRK_ENTITY_NO) INNER JOIN
Health_Activations ON Health_Prod1.Broker = Health_Activations.[Broker Entity]
GROUP BY Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region;



Duane Hookom said:
What is your SQL view of your query?

--
Duane Hookom
MS Access MVP
--

Benton said:
It doesn't seem to be working
Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the
problem?
This Average thing is wrong



John Spencer (MVP) said:
You may need to force the values being returned by NZ to be numeric. In
some
circumstances NZ returns a text value of the number and the plus sign
then acts
like a concatenation operator. So that 10+0+0+10+0 could return a string
of
1000100. And then the average function manages to turn that string into
a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))

Benton wrote:

I got a very funny situation here.I've calculated SUM for the
following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these
date
fields have got numeric values.I then summed each field (i.e 200407)
on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then selected
the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one
keeps
giving incorrect values.Yes I got zero values on some of my
fields.Could that
be the reason??The funny thing is when i test the dynaset results on
Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

Do you really have fields with date names?
I assume this expression is giving you a problem:
(Avg([200407])+(Avg([200408])+(Avg([200409])+(Avg([200410])+(Avg([200411])+(Avg([200412])+(Avg([200501])+(Avg([200502])+(Avg([200503])+(Avg([200504])))))))))))/10
AS [AVG]

I'm not sure what you want calculated. You might want to add all the values,
divide by 10 and then average this number:

Avg(
([200407]+[200408]+[200409]+[200410]+[200411]+[200412]+[200501]+[200502]+[200503]+[200504])/10)
AS [AVG]

--
Duane Hookom
MS Access MVP


Benton said:
Hi Duane, sorry 4 taking long. here's my sql view

SELECT Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region,
Sum(Health_History.[monthly average 2001/2002]) AS [SumOfmonthly average
2001/2002], Sum(Health_History.[monthly average 2002/2003]) AS
[SumOfmonthly
average 2002/2003], Sum(Health_Prod1.[200307]) AS SumOf200307,
Sum(Health_Prod1.[200308]) AS SumOf200308, Sum(Health_Prod1.[200309]) AS
SumOf200309, Sum(Health_Prod1.[200310]) AS SumOf200310,
Sum(Health_Prod1.[200311]) AS SumOf200311, Sum(Health_Prod1.[200312]) AS
SumOf200312, Sum(Health_Prod1.[200401]) AS SumOf200401,
Sum(Health_Prod1.[200402]) AS SumOf200402, Sum(Health_Prod1.[200403]) AS
SumOf200403, Sum(Health_Prod1.[200404]) AS SumOf200404,
Sum(Health_Prod1.[200405]) AS SumOf200405, Sum(Health_Prod1.[200406]) AS
SumOf200406, Sum(Health_Prod1.[200407]) AS SumOf200407,
Sum(Health_Prod1.[200408]) AS SumOf200408, Sum(Health_Prod1.[200409]) AS
SumOf200409, Sum(Health_Prod1.[200410]) AS SumOf200410,
Sum(Health_Prod1.[200411]) AS SumOf200411, Sum(Health_Prod1.[200412]) AS
SumOf200412, Sum(Health_Prod1.[200501]) AS SumOf200501,
Sum(Health_Prod1.[200502]) AS SumOf200502, Sum(Health_Prod1.[200503]) AS
SumOf200503, Sum(Health_Prod1.[200504]) AS SumOf200504,
Sum([Health_Prod1]![200407]+[Health_Prod1]![200408]+[Health_Prod1]![200409]+[Health_Prod1]![200410]+[Health_Prod1]![200411]+[Health_Prod1]![200412]+[Health_Prod1]![200501]+[Health_Prod1]![200502]+[Health_Prod1]![200503]+[Health_Prod1]![200504])
AS Expr111,
(Avg([200407])+(Avg([200408])+(Avg([200409])+(Avg([200410])+(Avg([200411])+(Avg([200412])+(Avg([200501])+(Avg([200502])+(Avg([200503])+(Avg([200504])))))))))))/10
AS [AVG]
FROM ((Health_Prod1 INNER JOIN Health_History ON Health_Prod1.Broker =
Health_History.[brk code]) INNER JOIN (Health_Linkages INNER JOIN Regions
ON
Health_Linkages.PRIMARY_DIST_CHNNL = Regions.[Dis channel number]) ON
Health_Prod1.Broker = Health_Linkages.BRK_ENTITY_NO) INNER JOIN
Health_Activations ON Health_Prod1.Broker = Health_Activations.[Broker
Entity]
GROUP BY Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region;



Duane Hookom said:
What is your SQL view of your query?

--
Duane Hookom
MS Access MVP
--

Benton said:
It doesn't seem to be working
Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the
problem?
This Average thing is wrong



:

You may need to force the values being returned by NZ to be numeric.
In
some
circumstances NZ returns a text value of the number and the plus sign
then acts
like a concatenation operator. So that 10+0+0+10+0 could return a
string
of
1000100. And then the average function manages to turn that string
into
a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))

Benton wrote:

I got a very funny situation here.I've calculated SUM for the
following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these
date
fields have got numeric values.I then summed each field (i.e
200407)
on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then
selected
the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one
keeps
giving incorrect values.Yes I got zero values on some of my
fields.Could that
be the reason??The funny thing is when i test the dynaset results on
Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks Duane
Now I know what the problem was, it works now.

Greatly appreciated.


Duane Hookom said:
Do you really have fields with date names?
I assume this expression is giving you a problem:
(Avg([200407])+(Avg([200408])+(Avg([200409])+(Avg([200410])+(Avg([200411])+(Avg([200412])+(Avg([200501])+(Avg([200502])+(Avg([200503])+(Avg([200504])))))))))))/10
AS [AVG]

I'm not sure what you want calculated. You might want to add all the values,
divide by 10 and then average this number:

Avg(
([200407]+[200408]+[200409]+[200410]+[200411]+[200412]+[200501]+[200502]+[200503]+[200504])/10)
AS [AVG]

--
Duane Hookom
MS Access MVP


Benton said:
Hi Duane, sorry 4 taking long. here's my sql view

SELECT Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region,
Sum(Health_History.[monthly average 2001/2002]) AS [SumOfmonthly average
2001/2002], Sum(Health_History.[monthly average 2002/2003]) AS
[SumOfmonthly
average 2002/2003], Sum(Health_Prod1.[200307]) AS SumOf200307,
Sum(Health_Prod1.[200308]) AS SumOf200308, Sum(Health_Prod1.[200309]) AS
SumOf200309, Sum(Health_Prod1.[200310]) AS SumOf200310,
Sum(Health_Prod1.[200311]) AS SumOf200311, Sum(Health_Prod1.[200312]) AS
SumOf200312, Sum(Health_Prod1.[200401]) AS SumOf200401,
Sum(Health_Prod1.[200402]) AS SumOf200402, Sum(Health_Prod1.[200403]) AS
SumOf200403, Sum(Health_Prod1.[200404]) AS SumOf200404,
Sum(Health_Prod1.[200405]) AS SumOf200405, Sum(Health_Prod1.[200406]) AS
SumOf200406, Sum(Health_Prod1.[200407]) AS SumOf200407,
Sum(Health_Prod1.[200408]) AS SumOf200408, Sum(Health_Prod1.[200409]) AS
SumOf200409, Sum(Health_Prod1.[200410]) AS SumOf200410,
Sum(Health_Prod1.[200411]) AS SumOf200411, Sum(Health_Prod1.[200412]) AS
SumOf200412, Sum(Health_Prod1.[200501]) AS SumOf200501,
Sum(Health_Prod1.[200502]) AS SumOf200502, Sum(Health_Prod1.[200503]) AS
SumOf200503, Sum(Health_Prod1.[200504]) AS SumOf200504,
Sum([Health_Prod1]![200407]+[Health_Prod1]![200408]+[Health_Prod1]![200409]+[Health_Prod1]![200410]+[Health_Prod1]![200411]+[Health_Prod1]![200412]+[Health_Prod1]![200501]+[Health_Prod1]![200502]+[Health_Prod1]![200503]+[Health_Prod1]![200504])
AS Expr111,
(Avg([200407])+(Avg([200408])+(Avg([200409])+(Avg([200410])+(Avg([200411])+(Avg([200412])+(Avg([200501])+(Avg([200502])+(Avg([200503])+(Avg([200504])))))))))))/10
AS [AVG]
FROM ((Health_Prod1 INNER JOIN Health_History ON Health_Prod1.Broker =
Health_History.[brk code]) INNER JOIN (Health_Linkages INNER JOIN Regions
ON
Health_Linkages.PRIMARY_DIST_CHNNL = Regions.[Dis channel number]) ON
Health_Prod1.Broker = Health_Linkages.BRK_ENTITY_NO) INNER JOIN
Health_Activations ON Health_Prod1.Broker = Health_Activations.[Broker
Entity]
GROUP BY Health_Linkages.BRK_ENTITY_NO, Health_Linkages.BRK_NAME,
Health_Linkages.PRIMARY_DIST_NAME, Regions.Region;



Duane Hookom said:
What is your SQL view of your query?

--
Duane Hookom
MS Access MVP
--

It doesn't seem to be working
Here's the query results:

200407 200408 200409 200410 Average
13968 0 14328 0 4716
36612 86868 38340 104520 1857.5

Yes numeric values are being stored in month fields? could that be the
problem?
This Average thing is wrong



:

You may need to force the values being returned by NZ to be numeric.
In
some
circumstances NZ returns a text value of the number and the plus sign
then acts
like a concatenation operator. So that 10+0+0+10+0 could return a
string
of
1000100. And then the average function manages to turn that string
into
a
number and ...

CDbl(Nz([200407],0))+ CDbl(Nz([200408],0))+ CDbl(Nz([200409],0))

Benton wrote:

I got a very funny situation here.I've calculated SUM for the
following
fields: ([200407]+[200408]+[200409]+[200410]+[200411]) each of these
date
fields have got numeric values.I then summed each field (i.e
200407)
on the
"Totals" row.

For Avg: Nz([200407],0)+Nz([200408],0)+Nz([200409],0). I then
selected
the
Average on "Totals" row.

The sum function seems to be working perfectly well and average one
keeps
giving incorrect values.Yes I got zero values on some of my
fields.Could that
be the reason??The funny thing is when i test the dynaset results on
Excel ,i
get the same values for both Sum and Average except for decimals

Plz help
Click to expand...
Click to expand...
Click to expand...
 
B

benton msimango via AccessMonster.com

I was trying to calculate the average across fields for a given month,i.e

(avg([200401]+ [200402] + [200403])/3).
 

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

Incorporating subquery into A query by SELECT 2
How to do a rolling average on a query that asks for input 8
calculate results show funny figures 3
dynamically change the calculation from Sum to Average 1
Query Math help 1
Update query average from second table 1
Average function 5
Average cells with #DIV/0! 4

Top