average

  • Thread starter Thread starter new kid
  • Start date Start date
N

new kid

I have a table with rows that are customer records and fields that are values
for different timeperiods, let's say months. How do I determine the average
for a certain number of months?

e.g "customer 1" - 100 - 500 - 300 - 200 - 400 - 300

I want to get the average of these numbers and be able report the result.
In this example 300.
 
On Sat, 21 Mar 2009 08:51:01 -0700, new kid <new
(e-mail address removed)> wrote:

The same way you just computed that average in your head:
select (field1 + field2 + field3 + field4 + field5 + field6)/6 as
myAverage from myTable

-Tom.
Microsoft Access MVP
 
new said:
I have a table with rows that are customer records and fields that
are values for different timeperiods, let's say months. How do I
determine the average for a certain number of months?

e.g "customer 1" - 100 - 500 - 300 - 200 - 400 - 300

I want to get the average of these numbers and be able report the
result. In this example 300.
Click to expand...

You will be better off getting your data into a more normalized
(less-spreadsheet) structure. This problem would be a piece of cake if your
table looked like this:
Customer DataMonth DataValue
1 2008-1 100
1 2008-2 500
etc.

The query for this would be a simple totals query whose sql would look like
this:

select customer, avg(DataValue) as MonthlyAverage
from goodtable

If you are unable to change your data to this structure for some reason (I
really urge you to do it if you can), then you will need to use a union
query to normalize it. I can't get much more specific than that since I
don't know the names of your fields or table.
 
On Sat, 21 Mar 2009 08:51:01 -0700, new kid <new
I have a table with rows that are customer records and fields that are values
for different timeperiods, let's say months. How do I determine the average
for a certain number of months?

e.g "customer 1" - 100 - 500 - 300 - 200 - 400 - 300

I want to get the average of these numbers and be able report the result.
In this example 300.
Click to expand...

It appears that you have fields for January, February, March etc. with a
number of hours in each field. That's a good spreadsheet design but - as you
are finding! - makes it MUCH more difficult to work in Access.

A properly normalized design has a "tall thin" structure, with fields like
CustomerID, MonthNo, and Hours. Rather than six *fields* in your table you
would store six *records* - then a simple Totals query will let you average,
or sum, or find the minimum or maximum, etc.

You can use a UNION query to recast your data into this normalized form. If
you'll post the actual fieldnames and datatypes of your table we can show you
the query you can use.
 
Back
Top