A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access, average several fields in one row



 
 
Thread Tools Display Modes
  #1  
Old March 16th, 2010, 10:46 AM posted to microsoft.public.access.gettingstarted
Mike DFR
external usenet poster
 
Posts: 27
Default Access, average several fields in one row

I have several rows of data in a field, I need to average all the entries in
one row
I have 12 fields for 12 months of data, I need the average of the sum of all
non blank entries.
For example 3 months completed, the solution in Excel is
(field1+field2+field3)/3
I am looking for method to average the sum in Access
  #2  
Old March 16th, 2010, 05:54 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Access, average several fields in one row

On Tue, 16 Mar 2010 02:46:01 -0700, Mike DFR
wrote:

I have several rows of data in a field, I need to average all the entries in
one row
I have 12 fields for 12 months of data, I need the average of the sum of all
non blank entries.


Then you have an incorrectly designed table.

For example 3 months completed, the solution in Excel is
(field1+field2+field3)/3
I am looking for method to average the sum in Access


Excel is a spreadsheet program, best of breed.
Access is a relational database development environment.
THEY ARE DIFFERENT!!!

Access is not "Excel on steroids"; it's a different program, with a different
design philosophy. Your table is a perfectly fine spreadsheet, but it's
completely inappropriate for a database - you're just finding out why!

What you ask can be done, but what you really should do is "Normalize" your
table. One big part of normalization is to get rid of repeating fields. Rather
than twelve *fields*, one for each month, a proper design would have twelve
*rows*, one amount for each, in a related table. If these are payments, you
would have a Payments table with a link to this table (I'm guessing it's a
table of accounts, or items paid for, or something of the sort), a PaymentDate
field (which you can use to identify the month), and an Amount field. You can
then do a very simple Totals query to average across any range of dates - a
full year, this year to date, or even the past twelve months (which will be
monstrously difficult in your current structure).

If you're going to use Access effectively, it's important to design your
tables to work with Access, rather than struggling against it! See:


Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials

--

John W. Vinson [MVP]
  #3  
Old March 16th, 2010, 08:49 PM posted to microsoft.public.access.gettingstarted
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Access, average several fields in one row

John Spencer wrote in
:

One way if you can't change your data is to use a VBA function.
I've posted one below. You would call it in a calculated field in
a query. Assuming your field names are the abbreviated month
names the expression might look like the following.

Field:
fRowAverage(Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oc t,Nov,Dec)

====== Copy and paste the following into a VBA module and save.
The module
must have a name other than fRowAverage


[code snipped]

Good function. I've added it to my collection of "immediate
functions", including iMax() and iMin(). I've renamed it iAve().

It also occured to me that if you didn't want to worry about passing
non-numeric values, you could do it without walking the array. The
code for that is after my sig.

Your version is more bulletproof, and for the size of array that is
the limit in a SQL statement, shouldn't be a performance issue. But
I thought it was fun to see what methods were available to total an
array of numbers. I do so love me my Split() and Join() functions!

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function iAve(ParamArray Values()) As Double
Dim strSum As String
Dim dblSum As Double
Dim lngItemCount As Long

strSum = Join(Values(), "+")
dblSum = Eval(strSum)
lngItemCount = UBound(Values()) + 1
iAve = dblSum / lngItemCount
End Function
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.