Help with Average

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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))
 
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...
 
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...
 
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...
 
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...
 
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...
 
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
Running average - subquery? 4
Compare current month totals with last months totals 1
calculate results show funny figures 3
dynamically change the calculation from Sum to Average 1
Rolling 3 Month Average 1
Average function 5
Query Math help 1

Back
Top