average

[kevcar40]

hi
I have a query that calculates the average for the year

i group the data by month

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns 134.1

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

YTD: SumOfQuantity
in the totals :avg

why does access return 134.1

thanks

kevin

[Roger]

Re: average

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

hi
I have a query that calculates the average  for the year
>
i group the data by month
>
i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1
>
however if i divide by 12 it returns 167.6
when i check the answer i get 167.6
>
YTD: SumOfQuantity
in the totals  :avg
>
why does access return 134.1
>
thanks
>
kevin

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data

[kevcar40]

Re: average

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:
>
>
>
>
>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>
2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -
>
- Show quoted text -

thanks for reply

i dont think i am averaging 15 months

i have place the query code below

TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','October',' November','Dece mber');


thanks
kevin

[Roger]

Re: average

On May 14, 4:57 am, kevcar40 <kevca...@btint ernet.comwrote:

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:
>
>
>
>
>

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -

>

- Show quoted text -

>
thanks for reply
>
i dont think i am averaging 15 months
>
i have place the query code below
>
TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','October', 'November','Dec ember');
>
thanks
kevin- Hide quoted text -
>
- Show quoted text -

so if you just run this query
SELECT Quantity_Query. years, Quantity_Query. months,
Quantity_Query. SumOfQuantity FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))

you get just 2 values, may and april ?
if that's the case the avg should be 2011/2 = 1005

[kevcar40]

Re: average

On 14 May, 16:11, Roger <lesperan...@na tpro.comwrote:

On May 14, 4:57 am, kevcar40 <kevca...@btint ernet.comwrote:
>
>
>
>
>

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -

>

- Show quoted text -

>

thanks for reply

>

i dont think i am averaging 15 months

>

i have place the query code below

>

TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','October' ,'November','De cember');

>

thanks
kevin- Hide quoted text -

>

- Show quoted text -

>
so if you just run this query
SELECT Quantity_Query. years, Quantity_Query. months,
Quantity_Query. SumOfQuantity FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
>
you get just 2 values, may and april ?
if that's the case the avg should be 2011/2 = 1005- Hide quoted text -
>
- Show quoted text -

i agree
but for some reason when i run the crosstab query it returns 134.1

[Roger]

Re: average

On May 15, 3:18 am, kevcar40 <kevca...@btint ernet.comwrote:

On 14 May, 16:11, Roger <lesperan...@na tpro.comwrote:
>
>
>
>
>

On May 14, 4:57 am, kevcar40 <kevca...@btint ernet.comwrote:

>

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -

>

- Show quoted text -

>

thanks for reply

>

i dont think i am averaging 15 months

>

i have place the query code below

>

TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','October ','November','D ecember');

>

thanks
kevin- Hide quoted text -

>

- Show quoted text -

>

so if you just run this query
SELECT Quantity_Query. years, Quantity_Query. months,
Quantity_Query. SumOfQuantity FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))

>

you get just 2 values, may and april ?
if that's the case the avg should be 2011/2 = 1005- Hide quoted text -

>

- Show quoted text -

>
i agree
but for some reason when i run the crosstab query  it returns 134.1- Hide quoted text -
>
- Show quoted text -

and if you don't use a crosstab, just a regular query
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years

what do you get ?

[kevcar40]

Re: average

On 15 May, 11:11, Roger <lesperan...@na tpro.comwrote:

On May 15, 3:18 am, kevcar40 <kevca...@btint ernet.comwrote:
>
>
>
>
>

On 14 May, 16:11, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 4:57 am, kevcar40 <kevca...@btint ernet.comwrote:

>

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -

>

- Show quoted text -

>

thanks for reply

>

i dont think i am averaging 15 months

>

i have place the query code below

>

TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','Octobe r','November',' December');

>

thanks
kevin- Hide quoted text -

>

- Show quoted text -

>

so if you just run this query
SELECT Quantity_Query. years, Quantity_Query. months,
Quantity_Query. SumOfQuantity FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))

>

you get just 2 values, may and april ?
if that's the case the avg should be 2011/2 = 1005- Hide quoted text-

>

- Show quoted text -

>

i agree
but for some reason when i run the crosstab query  it returns 134.1- Hide quoted text -

>

- Show quoted text -

>
and if you don't use a crosstab, just a regular query
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
>
what do you get ?- Hide quoted text -
>
- Show quoted text -

hi
i get the same answer 134.1

[Roger]

Re: average

On May 16, 3:40 am, kevcar40 <kevca...@btint ernet.comwrote:

On 15 May, 11:11, Roger <lesperan...@na tpro.comwrote:
>
>
>
>
>

On May 15, 3:18 am, kevcar40 <kevca...@btint ernet.comwrote:

>

On 14 May, 16:11, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 4:57 am, kevcar40 <kevca...@btint ernet.comwrote:

>

On 14 May, 11:33, Roger <lesperan...@na tpro.comwrote:

>

On May 14, 12:52 am, kevcar40 <kevca...@btint ernet.comwrote:

>

hi
I have a query that calculates the average  for the year

>

i group the data by month

>

i have 2 values for April and may
April = 1908
May = 103
sum = 2011
if i use the avg function it returns  134.1

>

however if i divide by 12 it returns 167.6
when i check the answer i get 167.6

>

YTD: SumOfQuantity
in the totals  :avg

>

why does access return 134.1

>

thanks

>

kevin

>

2011 / 134.1 = 15
without seeing the query or all the data, it looks like you've 15
months of data- Hide quoted text -

>

- Show quoted text -

>

thanks for reply

>

i dont think i am averaging 15 months

>

i have place the query code below

>

TRANSFORM Sum(Quantity_Qu ery.SumOfQuanti ty) AS SumOfQuantity
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years
PIVOT Quantity_Query. months In
('January','Feb ruary','March', 'April','May',' June','July','A ugust','Septemb er','Octob er','November', 'December');

>

thanks
kevin- Hide quoted text -

>

- Show quoted text -

>

so if you just run this query
SELECT Quantity_Query. years, Quantity_Query. months,
Quantity_Query. SumOfQuantity FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))

>

you get just 2 values, may and april ?
if that's the case the avg should be 2011/2 = 1005- Hide quoted text -

>

- Show quoted text -

>

i agree
but for some reason when i run the crosstab query  it returns 134.1-Hide quoted text -

>

- Show quoted text -

>

and if you don't use a crosstab, just a regular query
SELECT Quantity_Query. years, Avg(Quantity_Qu ery.SumOfQuanti ty) AS YTD
FROM Quantity_Query
WHERE (((Quantity_Que ry.years)='2008 '))
GROUP BY Quantity_Query. years

>

what do you get ?- Hide quoted text -

>

- Show quoted text -

>
hi
i get the same answer  134.1- Hide quoted text -
>
- Show quoted text -

don't know what's happening
I created a table with columns year,month,qty
filled it with 2008,Apr,1908
2008,May, 103

ran this query
TRANSFORM Sum(tblMonth.qt y) AS SumOfqty
SELECT tblMonth.year, Avg(tblMonth.qt y) AS AvgOfqty
FROM tblMonth
WHERE (((tblMonth.yea r)=2008))
GROUP BY tblMonth.year
PIVOT tblMonth.month;

and got
year avg apr may
2008 105.5 1908 103

which is correct

maybe your db is corrupt ? and an index is corrupt ?