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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|