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] |
Thread Tools | |
Display Modes | |
|
|