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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Dummy in Access for Dummies - 16 Nov 2006 00:18 GMT

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

Reply to this Message

Duane Hookom - 16 Nov 2006 02:48 GMT

Are you attempting to average fields across a single record? Adding columns
across generally suggests an un-normalized table structure. How about
providing some sample values and desired results.

Signature

Duane Hookom
MS Access MVP

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

Reply to this Message

The Dummy in Access for Dummies - 16 Nov 2006 18:36 GMT

Sure...in a table...column1=Item...column2-column13=sales per month. In query
design, I wanted to add a column14 and column15 to have the average and stdev
for each item (row). I though that all I needed to do was enter
Avg([column2]+...[column13]) and then run the query for the average sales
that year for each row.

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

Reply to this Message

Duane Hookom - 17 Nov 2006 04:59 GMT

It doesn't work that way. You are creating a spreadsheet. Either move your
data to Excel or maybe normalize so that the month value becomes a value in
a Mth field rather than a column in a table. Once your table is normalized,
you can easily create averages and standard deviations.

If you can't change your table structure, create a union query that
normalizes. Use the union query as the source for a totals query.

Signature

Duane Hookom
MS Access MVP

> Sure...in a table...column1=Item...column2-column13=sales per month. In
> query
[quoted text clipped - 5 lines]
>
>> why do I get just the sum with Avg([column1]+[column2]...)

Reply to this Message