why do I get just the sum with Avg([column1]+[column2]...)

[Guest]

why do I get just the sum with Avg([column1]+[column2]...)

 

[Guest]

You shouldn't, you should get the average... Are you checking that column1
and column2 contain data (ie: are not null)?

Damian.

 

[Rick Brandt]

why do I get just the sum with Avg([column1]+[column2]...)

Aggregate functions in databases aggregate values in a single column across many
rows. They do not aggregate multiple arguments fed into them. The "average" of
summing a bunch of fields is the same as the sum because there is only one value
to take the average of.

Needing to aggregate like this (usually) suggests a flawed database design that
is more like a spreadsheet than a proper database table.

 

[John Vinson]

why do I get just the sum with Avg([column1]+[column2]...)

Are you expecting Avg() to average the values of Column1, Column2,
etc.? It won't; Avg() averages across RECORDS not fields, and you're
not passing a list of fieldnames anyway - you're adding all the values
and then averaging that sum.

What's actually in your table?

What result do you expect?

John W. Vinson[MVP]

 

[Guest]

column1 and column2 and the other 10 months, contain numbers representing
sales figures. I wanted to get the average and StDev on each row
(representing one particular item)...but I only get the sum of the numbers on
each row.

You shouldn't, you should get the average... Are you checking that column1
and column2 contain data (ie: are not null)?

Damian.

why do I get just the sum with Avg([column1]+[column2]...)

 

[Guest]

How should I approach this then. I just wanted to get the average and
standard deviation from each column, for each row. Each column represents
sales per month and each row represents a particular item....I wanted the
yearly average and StDev. in the query design.

why do I get just the sum with Avg([column1]+[column2]...)

Aggregate functions in databases aggregate values in a single column across many
rows. They do not aggregate multiple arguments fed into them. The "average" of
summing a bunch of fields is the same as the sum because there is only one value
to take the average of.

Needing to aggregate like this (usually) suggests a flawed database design that
is more like a spreadsheet than a proper database table.

 

[Guest]

Each column has the monthly sales (column1=Jan's). Each row is for a
particular item. In query design, I wanted to get the average and standard
deviation for each row (item); based on the values for each column. Any idea

why do I get just the sum with Avg([column1]+[column2]...)

Are you expecting Avg() to average the values of Column1, Column2,
etc.? It won't; Avg() averages across RECORDS not fields, and you're
not passing a list of fieldnames anyway - you're adding all the values
and then averaging that sum.

What's actually in your table?

What result do you expect?

John W. Vinson[MVP]

 

[John Vinson]

Each column has the monthly sales (column1=Jan's). Each row is for a
particular item. In query design, I wanted to get the average and standard
deviation for each row (item); based on the values for each column. Any idea
on how I should approach this?

By correctly normalizing your table.

You have a one (item) to many (sales) relationship. The correct
structure uses TWO TABLES:

Items
ItemID
ItemDescription
<other info about the item itself>

ItemSales
ItemID <link to Items>
SaleDate <the first of the month if you're recording monthly sales>
Amount

Rather than twelve FIELDS (in a spreadsheet-like design) you would
have twelve RECORDS in the ItemSales table. In fact... you could have
46 records if you're recording sales from 2003, 2004, 2005 and 2006 up
to last month!

You can then use a Totals Query to calculate Avg or STDev; these
aggregate functions DO work just fine, if you're averaging across
records.

John W. Vinson[MVP]

 

[Guest]

Thanks for the advise, I believe I'll try another way...the problem is that I
am only extracting the data with ACCESS, from a another database (which
doesn't allow editing or writing). I'll have to search for the table that has
that formatting (records), rather then the spreadsheet table they are using.

Cheers,

 

[John Vinson]

Thanks for the advise, I believe I'll try another way...the problem is that I
am only extracting the data with ACCESS, from a another database (which
doesn't allow editing or writing). I'll have to search for the table that has
that formatting (records), rather then the spreadsheet table they are using.

You can use a "Normalizing Union Query" to extract the data from the
spreadsheet into a tall-thin table. If you'll post the fieldnames of
your table we'd be able to suggest the design of such a query.

John W. Vinson[MVP]