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
0
Utf
3/16/2010 9:46:01 AM
access.gettingstarted 618 articles. 1 followers. Follow

3 Replies
944 Views

Similar Articles

[PageSpeed] 48

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,Oct,Nov,Dec)

====== Copy and paste the following into a VBA module and save.  The module 
must have a name other than fRowAverage
=========================================================================
Public Function fRowAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call:
'myAvg = fRowAverage("1","TEST","2", "3",4,5,6,0) returns 3 (21/7)
'Ignores values that cannot be treated as numbers.
'
'   Max of 29 arguments can be passed to a function in Access SQL

Dim i As Integer, intElementCount As Integer, dblSum As Double
    intElementCount = 0
    dblSum = 0

    For i = LBound(Values) To UBound(Values)
       If IsNumeric(Values(i)) Then           'Ignore Non-numeric values
          dblSum = dblSum + Values(i)
          intElementCount = intElementCount + 1
       End If
    Next i

    If intElementCount > 0 Then    'At least one number in the group of values
       fRowAverage = dblSum / intElementCount

    Else    'No number in the group of values
       fRowAverage = Null
    End If

End Function

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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.
> 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
0
John
3/16/2010 12:43:23 PM
On Tue, 16 Mar 2010 02:46:01 -0700, Mike DFR
<MikeDFR@discussions.microsoft.com> 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]
0
John
3/16/2010 4:54:07 PM
John Spencer <spencer@chpdm.edu> wrote in
news:en1ZFZQxKHA.4240@TK2MSFTNGP06.phx.gbl: 

> 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,Oct,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
0
David
3/16/2010 7:49:46 PM
Reply:

Similar Artilces:

What is the cell data reference for 5th row and 7th column called
Can someone help me with this question? I need to get the answer for my homework. Thanks for your help. I'm guessing, but might it be G5? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Shy-di@verizon.net" <Shy-di@verizon.net@discussions.microsoft.com> wrote in message news:A0979494-228B-435E-98F7-6BD26C900266@microsoft.com... > Can someone help me with this question? I nee...

Missing row #2
A user has deleted a row that had an amount in it. Her rows are now number, 23, 24, 25, 27 (so row 26 is missing). Whenever she tries to total columns or rows, the number that is in that missing row is calculated in. How do I retrieve that row, or clear out the number? TIA. Kim Select rows 24 and 26, the whole rows and goto Format>Row>Unhide. -- HTH RP (remove nothere from the email address if mailing direct) "Kim" <Kim@discussions.microsoft.com> wrote in message news:37603B87-33DA-4EB0-AC54-68F919F4A8B8@microsoft.com... > A user has deleted a row that ha...

Report generated from text box fields
hello. i have a 3 column report. The 3 products compared on the report are assigned when the report opens and asks the user for the ID.. is there are way to create 3 unbound text fields and have the report compare the IDs of the products listed in the text box.. i have done something similar that searches based on what was entered in a text box, but wasnt sure how to have the report pick up three different text boxes.... this is an example of what i have in another application that opens up a query Like "*" & [forms]![SEARCHMENU]![text37] & "*" -- Message po...

Choose highest value in a row
How do I tell excel to choose the highest value in a range of cells? A B C D E Highest 1 3 2 7 5 7 help please. =max(a1:e1) will return the highest value. sohum.shah@gmail.com wrote: > > How do I tell excel to choose the highest value in a range of cells? > > A B C D E Highest > 1 3 2 7 5 7 > > help please. -- Dave Peterson =MAX(A1:E1) returns 7 in your example. Gord Dibben MS Excel MVP On 23 Oct 2006 10:47:09 -0700, sohum.shah@gmail.com wrote: >How do I tell excel to choose the highest valu...

"Access denied" frm Vista client to Win2003 server, but not XP or
The setup: -A Win2003 server at a hosting site, configured for VPN access. -The Win2003 server has 2 users configured, e.g. Bill and Fred. Each has the 'Allow access' selection for "Remote Access Permission (Dial-in or VPN)" on their individual properties page. -The Win2003 server has 3 shared folders, with share names "Everybody", "Bills Folder", and "Freds Folder", as implied, the 'Everyone' group has read access via the 'Permissions' settings of the shared directory properties. Bill and Fred each have full ...

Need Formula to Average Monthly Increase
I have a spreadsheet where there are columns for each month of the year Each cell contains a number of loan submissions that graduall increases per month as we gain more business from each mortgage vendor I need a formula that can look at multiple cells in a row (i.e Jan-Dec) per vendor and then give me a result that shows the averag monthly percentage increase factor in submissions... Example: MONTH Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec A SUBS 1 5 18 29 30 40 57 85 150 275 ......... ..I will be using this data fo forecasting future submissions ...

Need to separate multiple numbers in one cell
I have multiple number in one cell and i need to somehow put each number into one separate cell for each number: I have : column A row1 2 5 8 10 I want: column B column C column D column E row1 2 5 8 10 How do I do it on Excel 2007? thanks EggHeadCafe - Software Developer Portal of Choice Map Stored Procedure Output To Class Properties http://www.eggheadcafe.com/tutorials/aspnet/2853a1aa-5db7-40d0-9cde-46847fa770ef/map-stored-procedure-outp.aspx Hi, Use Data > Text to columns and specify the delimiter as space ...

Getting 'read' access to all domain mailboxes?
Is it possible setup one user to have full read access to all our domain mailboxes? I have read about delegate access but I just want to assign one users (our personnel manager) to have read access to all mailboxes Again, I only want this one person to have 'read only' access to all the other mailboxes so she can read their mail etc Many thanks in advance - ras - ...

All-In-One Outlook View
I was suprised not to see a view in Outlook where I can see - email, calander and tasks. I can create just about what I need, however, I thought I had seen third party software that can show you everthing on one view ( screen) Ron ...

How to access help?
That office assistant thing is useless to me. It says you can switch it off but how? And where do I find stock standard help? Or does that only come up when you've got the paperclip switched off? In which case, again, how do I switch the thing off? How to modify or remove Office Assistant Help in Office 2000 http://support.microsoft.com/kb/203689/en-us -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "abrogard" <abrogard@discussions.microsoft.com> wrote in message news:36DF541B-4845-454E-B610-4BA5DFD28952@mi...

How can I remove the previous lock cells and keep the new ones.
Could you please help me?... I locked and protected the cells A1:H20 in the sheet 1 two months ago. However, yesterday I wanted to unlock and unprotect these cells and locked and protected the cells A21:H40 instead. However, for some reason the cells A1:H20 are still lock and protect. How can I remove the previous lock cells (A1:H20) and keep the new cells (A21`:H40) only. Thanks. Maperalia In EXCEL 2007 take the following actions to unlock cells A1 to H20:- 1. Home / Cells group / Format / Unprotect Sheet / enter the password to Unprotect Sheet / OK / highlight cells A1 t...

Validation Rule for IBAN Account number in MS Access Form field
Hi, In my table (and the form field linked to it) I have a field for the IBAN account number, which must be 2 Upper case characters A to Z, followed by 18 manadatory digits, and up to 28 total. So in my input mask I have this: >LL00\-0000\-0000\-0000\-0000\-9999\-9999;; and it works fine. But when the user enters less than 20 characters total (including the two letters at the start and at least 18 digits) Access gives a very unfriendly ''The value you entered isn't appropriate for the input mask '>LL00\-0000\-0000\-0000\-0000\-9999\-9999;;' specified for thi...

XSD One Multiple element question
Hi, I am trying to define a Key element for a vehicle schema. I have three ways to uniquely identify a vehicle such as URN, UnitID and Vin. I'd like to make a rule that ensures that at least one of the three is present, but 2 or all 3 can be present. I tried something like below, but it does not seem valid. Thanks in advance for any assistance anyone may be able to provide. Portion of schema is posted below. Hopefully it gives some idea as to what I am trying to accomplish. Best regards, Victor <xs:complexType name="VehKeyType"> <xs:choice> <xs:seq...

CRM 4.0
Hi! We have just made a migration from CRM 3.0 to 4.0, and everything went well, except one thing - field Resources is disabled (like it's in read-only mode, but it's not!), and we can't find the way to enable it... Any suggestions....? Best regards, Ivica I'm sure this is a bug - we had it on the fax entity with "to" - Team please take note I modifed the xml - after i look ed at a clean xml to see the diffence Pete "Ivica Ivancic" <ivica.ivancic@infinitas.hr> wrote in message news:5534F8F2-930B-45F1-A503-4D4DBA092E0F@microsoft.com....

data available to MS Access
I'm not able to see the multiple entries of a sharepoint item with a mulit-line text column --I can only access the text of the last entry. With other multi-value columns (attachments, single-line text, numbers, etc), I don't have a problem getting the multiple entries. ...

Macro-separate different dates with two grey rows
Excel 2000: In column B, there are about 200 rows with dates. The sheet is sorted by date. The dates have a range of about 3 weeks. There may be between 1 and 35 rows with the same date. Is there a way to automatically insert 2 grey rows between the different dates? Example: I enter 21april in row 1, then 22april in row 2, and 2 grey rows separate automatically. Joe Hi Joe try the following macro Sub insert_rows() Dim lastrow As Long Dim row_index As Long lastrow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row For row_index = lastrow - 1 To 1 Step -1 If Cells(row_index, &...

Program accessing address book
I get the warning below when an application uses Outlooks address book. How do I turn off this warning for good? A program is trying to access your Address Book or contacts. Thanks You may not be able to turn it off, check here for some options http://www.slipstick.com/outlook/esecup.htm#autosec "Jason" <jason.strand@columbialegal.org> wrote in message news:013101c35aad$28167bb0$a401280a@phx.gbl... > I get the warning below when an application uses Outlooks > address book. How do I turn off this warning for good? > > A program is trying to access your Address B...

Finding unique items in data field for pivot tables
I have a huge pivot table I'm trying to create and for a critical item I select I get the following message: A field in your data source has more unique items that can be used in a PivotTable. Microsoft Excel may not be able to create a PivotTable or may create the PivotTable without the data from this field. Any ideas how I can find the duplicates and create the PivotTable with this field? Hello- Based on the message, 'duplicates' is not your problem, *Unique* items is. Pivot tables are limited to 32,500 unique items per field. So if you're analyzing product sales, for...

Chart changing based on change in data source (number of rows/colu
As I see, the data range for charting is normally fixed. I have to produce chart based on chaning rows and/or columns in data source table. Has anybody got some suggestion for me? Bal Ram Bhui - For Excel charts in general, I suggest Jon Peltier's web site: http://www.peltiertech.com/ For dynamic charts, I suggest: http://www.peltiertech.com/Excel/Charts/Dynamics.html - Mike Middleton http://www.DecisionToolworks.com Decision Analysis Add-ins for Excel "Bal Ram Bhui, Jakarta" <Bal Ram Bhui, Jakarta@discussions.microsoft.com> wrote in message news:DD0B5564-26B0-4...

One more Newbie Question
We would like to print the customer's name on the receipt. I have no idea how to do that. It feeds too much blank paper through - after each transaction it feeds about 4" of blank paper. How do you stop it printing the credit card receipt info on the receipt? We use a separate credit card machine that prints its own receipt. It would be nice not to have to enter any credit card info, except maybe the approval code, but RMS forces us to enter the credit card info before being able to processing the transaction - a real drag. Thanks for the help! -- Bud Izen ...

Cannot access controls in code behind
Hi, I am developing a Web application using Visual Studio 2008 SP1 and C# . I am using the non-project option (website as opposed to web object). Occassionally when I place a control in the aspx page, I find that it is not available in code behind. Intellisense doesn't locate the control, and if I simply type it in, I get an error that the variable is not defined. What gives? Thanks On May 5, 8:43=A0am, Crazy Cat <danbr...@hotmail.com> wrote: Never mind -- it was my fault. Thanks for reading. > Hi, > > I am developing a Web application using V...

Record (row) count by Table in Database
I have a rather critical need to monitor the number of rows for each table in a database. The Database contains 17 tables, and at any given time any one of the tables can contain zero (0) rows or several thousand rows. I was thinking along the lines of creating another table which would contain: Table Name, Date/Time and the Row Count. and appending the row count for each table via a summary query. This way, I could see not only the number of rows in each table at a given point of time but also look at the 'trend' by table. I have two problems with this approach, the f...

how to access to a shared folder on a NAS server
In my AD networks (N.1 Windows server 2003 DC and N. 5 Windows XP domain clients) there is a shared folder on a NAS server (Synology DS 101). The access to this shared folder is restricted to some users defined on the NAS server. This username on the Synology matches the username defined on the AD server. The problem is that when I login on the Windows XP client with one of the username defined even on the NAS server I can access without any problem the shared folder on the NAS server from the Windows XP client. If I login to the Windows XP client with a username that is not define...

Can not access CRM 4.0 from a client machine after initial setup
Hi, I just finished installing CRM 4.0 and the client side. When I go through the wizard to connect the server, it is not accepting the intranet address, "The specified MS CRM server is not available. can not access through the browser either. I can access it while logged onto the server. I did create the domain user onto the CRM and gave all roles. The address is: "http://crm:5555/CPA-CRM/loader.aspx". I am running SBS 2008, and a separate server for CRM/SQL2005. Another question: How can I change it where it is only "http://crm/loader.aspx" thanks, Joe ...

Use Access on the Internet
I have a database created in Microsoft Access. I also have a website with webspace. Can I make it so that people can use the database on the website and if so how? My webspace provider provides SQL and CGI support are these relevant to my question? -- Hagan Your provider needs to also support Access. If he doesn't, you can convert your Access tables to SQL Server and leave your Access front end in tact, linked to the SQL server database rather than the Jet database. Do read the KB article about client/server performance to see if you need to optimize your app to work more effici...