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!
11  3515 
Are you trying to get the average of the five fields in a single row?
-- CK
Yes - At least for each student record.
What do you have so far?
--- CK
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.
Depends on where you need the data. You can also add a COMPUTED column on your table.
--- CK
I would think that would work CK. Guess my issue would still be what would give the average of 5 numerical fields, excluding nulls?
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
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.
try something like this: -
select
-
(isnull(col1,0) + isnull(col2,0) + isnull(col3,0) + isnull(col4,0) + isnull(col5,0)) /
-
(isnull(col1/col1,0) + isnull(col2/col2,0) + isnull(col3/col3,0) + isnull(col4/col4,0) + isnull(col5/col5,0)) as COL_AVERAGE
-
from yourtable
-
--- CK
CK, should I be using this code on the Access/VB script side of things or on the backend SQL side? Thanks!
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mark |
last post by:
I am producing a report that requires me, among many other things, to
calculate the average contracts for about 1000 companies during the yr
2003. The "quirk" is that all the companies do not have...
|
by: Olav.NET |
last post by:
I am a .NET/C++ developer who is supposed to do some work with Access.
I do not know much about it except for the DB part.
Questions:
*1*
I am looking for INTENSIVE books to get quickly up to...
|
by: David Isaac |
last post by:
Is it expected for access to set elements to be much
slower than access to list elements? Explanation?
Thanks,
Alan Isaac
9.806250235714316
3.9823075279120701
|
by: grgimpy |
last post by:
My form has two main text boxes that are not working correctly with the code I wrote. Upon opening the form, the operator must select a "Part Number" from a list box. Once a "Part Number" is...
|
by: KishenGajjar |
last post by:
I'm trying to convert a query that runs in Query Analyser (SQL Server 2000) to work in MS Access. This will then help me modify the query further and use it to create reports and such.
This query...
|
by: JJ R |
last post by:
I am trying to develop a simple and efficient to calculate averages. For
example I want to calculate the averages of the Values for rows 1 and 2
and use ID 1 as the row with the average result.
...
|
by: Roberto Mora |
last post by:
I have not done programming in a very long time and what is worst, I never learned VB. Although my job does not require this knowledge, I cam across a problem that although it seemed simple it has...
|
by: nckinfutz |
last post by:
hello, I am having a problem with an access database. this is not my database and I did not create it, nor am I very good at access. however, I am a network engineer and that is why this problem...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
