Calculate Average of Multiple Fields

[Boreal2009]

Hello all,
I am attempting to calculate the average of 7 fields of data (eg: DataColumn1, DataColumn2, etc...). The average field (DataColumnAVG) is included in the same table as the input data.

I need the average to calcuate the same way that excel calculates an average of data cells - that is, null values must be ignored. Example: DataColumn1=0.5, Datacolumn2=0.3, DataColumn3=0.6, DataColumn4 thru 7 = null. DataColumnAVG = (0.5+0.3+0.6)/3.

I am currently trying to do this via an update query using the build function. Please be aware that I have no experience with VB code and that I am trying to do this in a GUI format. If this is not possible in a GUI world, explicit step by step directions would be needed for me to do this via VB code.

Thanks in advance for any help!

 

[Brianwarnock]

I'm sure that this question has been asked before, maybe a year ago, try a search.

Brian

 

[Brianwarnock]

OK it was March this year titled Average of various fields

Brian

 

[dbDamo]

However you should not be storing a calculated field in your table...

 

[boblarson]

However you should not be storing a calculated field in your table...

At least not until Access 2010 comes out

 

[dbDamo]

I haven't read much about 2010, we're still using 2002. Big changes then?

 

[boblarson]

I haven't read much about 2010, we're still using 2002. Big changes then?

Quite. If you haven't gotten onto 2007 yet, it will be even bigger because you'll have to get used to all of the ribbon and other new features. But in 2010 we have Data Macros (basically like SQL Triggers) and we also have special fields called Calculated Fields (will be necessary to use these for some of the not yet disclosed functions).

 

[Brianwarnock]

Hadn't noticed that he wanted to store it in his table.

As for 2010 heck i'll probably give in rather than go to 2007, I only use Access on the forum now, and as the little knowledge I had fades I'll retire properly and watch paint dry.

Brian

 

[dbDamo]

I've been playing around with 2007 for a few weeks now so that its not too much of a shock to me when we do upgrade or if I move to an organisation that isn't so far behind!!

I'm getting more used to it, but still prefer 2002 atm. I'm assuming 2010 has a very similar interface to 2007 which I won't enjoy, things seem to be scattered randomly all over the place!!

 

[boblarson]

things seem to be scattered randomly all over the place!!

Actually they aren't scattered randomly. But, it is hard for someone used to things in the old way to find what they want, but it has been shown that for NEW users, they can find many of the things, previously hard to find in the old menu structure, much easier.

So, anyway, it gets much easier as you get more used to it. And, if you think about it, after you get used to it, you will likely find that things are actually in a logical place, whereas it wasn't as logical before (you just knew where they were because you were used to it and had lots of time in on using it that way).

Yes, 2010 will be much like the 2007 interface, although they have made some improvements on the Ribbon and such.

 

[dbDamo]

I might have to get myself a copy early on then so I don't get left behind