By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

SQL / Access (Average Code)

P: 14
Currently running SQL Server 2005 with Access as the frontend. I'm trying to get the numerical average of 5 separate fields within one table. (Excluding NULL fields). I would like to display the average on the form our academics department views in Access. I cannot for the life me decide on if this would be best accomplished in some sort of view in SQL or just in Access on the VB side. I also was having trouble finding the basic code I would use to process this data. Any help would be greatly appreciated, thanks!
Mar 25 '09 #1
Share this Question
Share on Google+
11 Replies


ck9663
Expert 2.5K+
P: 2,878
Are you trying to get the average of the five fields in a single row?

-- CK
Mar 25 '09 #2

P: 14
Yes - At least for each student record.
Mar 25 '09 #3

ck9663
Expert 2.5K+
P: 2,878
What do you have so far?

--- CK
Mar 26 '09 #4

P: 14
I currently just have the SQL table(s). Have no code. That's the problem, I'm not sure if this would best be done in Access using VB or on the SQL side using a view.
Mar 26 '09 #5

ck9663
Expert 2.5K+
P: 2,878
Depends on where you need the data. You can also add a COMPUTED column on your table.

--- CK
Mar 26 '09 #6

P: 14
I would think that would work CK. Guess my issue would still be what would give the average of 5 numerical fields, excluding nulls?
Mar 26 '09 #7

ck9663
Expert 2.5K+
P: 2,878
I'm sorry I'm a little slow.

Add those five columns and divide the sum with 5. Or are you considering excluding those NULLs, ie if one of the column is NULL, you only divide it by 4?


--- CK
Mar 26 '09 #8

P: 14
I'm trying to get the numerical average of the 5 separate fields. If any one of those fields are null I would not that field to be included in the calculation.
Mar 26 '09 #9

ck9663
Expert 2.5K+
P: 2,878
try something like this:

Expand|Select|Wrap|Line Numbers
  1. select
  2. (isnull(col1,0) + isnull(col2,0) + isnull(col3,0) + isnull(col4,0) + isnull(col5,0)) /
  3. (isnull(col1/col1,0) + isnull(col2/col2,0) + isnull(col3/col3,0) + isnull(col4/col4,0)  + isnull(col5/col5,0)) as COL_AVERAGE
  4. from yourtable
  5.  

--- CK
Mar 26 '09 #10

P: 14
CK, should I be using this code on the Access/VB script side of things or on the backend SQL side? Thanks!
Mar 31 '09 #11

ck9663
Expert 2.5K+
P: 2,878
That's a T-SQL code. You're going to have to convert to Access/VBS if you want it on your front-end.


--- CK
Mar 31 '09 #12

Post your reply

Sign in to post your reply or Sign up for a free account.