is this query possible???????

[filo77]

Is it possible to create a sum/avg query on a row instead of a column?

I have a table where records store test data per studentID, for example:

------------------------------------
field1 | field2 |field3 | field4 | field5
-----|-------|------|------|-------
id5 | 80 | 60 | null | 70
------------------------------------

can I create a query that calculates the avg of all test results for this student id, and if yes, how do I ignore null values?

Is this possible or the design is flawed???

Thanks.

 

[KenHigg]

It is possible, but I suspect your design is flawed. Can you share the tables and their fields?

 

[filo77]

Sorry

Unfortunately I can not share the tables, because the datbase is not mine and the owner is paranoid about the data in it. (the design is not mine either)

Can you still help me?????????

 

[rockman]

You can create a calculated field within the query:

SELECT tblStudents.*, ([field1] + [field2] + [field3])/3 as fldAverageScore
FROM tblStudents

As per eliminating Nulls (if I understand you), you would replace the number "3" with a condition that evaluates each field and if it exists adds 1 to the denominator.

HTH,
Jeff

 

[filo77]

conditions in the select clause

Thanks rockman.

I had a problem yesterday:
in the select clause I had an expression like the one you suggested and it was returning 0 if one of the fields was null. So if I had select (field1+field2) as Expr1, and if they were not null it would display the result, but if I create a query select select (field1+field2+field3) as Expr1, and if field3 is null it returned 0. Any ideas why?

So you are saying that you can have conditions in the select clause and also increment variables?? Is this standard SQL or I need procedures as well.

Thanks very much for the help. The owner of the database is so stubborn about changing the design, when it is so obvious that it is flawed.

filo

 

[Pat Hartman]

If the table were properly structured, you could use a simple Avg() and that would account for nulls so the average of 3,null,3 would be 3 but the average of 3,0,3 would be 2.

You will need to create your own function if you want to calculate "across". Relational databases do not support repeating groups so they do not provide "across" functions. The table was probably copied from a spreadsheet and not properly normalized.

When you do arithmetic with fields that may contain null values, you need to handle the problem yourself. Use the Nz() function -
Nz(fld1,0) + Nz(fld2,0) + Nz(fld3,0)

Hope you don't have too many columns or you're in for a lot of coding.

 

[rockman]

Pat (once again) makes great points:

Nulls will disrupt your calculations, so encase anything that could be a Null in Nz().

Admittedly, the grades should be in another table with a relational reference so that if you decide to have 4, 5, or 15 scores your code could easily accomodate.

But if you are comfortable with the current design and limitations, this should work for you:

SELECT tblStudents.*, (Nz([field1]) + Nz([field2]) + Nz([field3]))/(IsNull(field1) + 1 + IsNull(field2) + 1 + IsNull(field3) + 1) as fldAverageScore
FROM tblStudents

This code takes advantage of the fact that the IsNull() function returns -1 or 0 based on the condition. Therefore,

IsNull(Null) + 1 = 0

and

IsNull(4) + 1 = 1

BEWARE: if all 3 exam scores are Null then you have a division by 0 error. You could surround the whole calculation with a IIf() to test for this, but if you thought the code above looked "busy"... try incorporating the IIf() function.

HTH,
Jeff

 

[abezuide]

lol yeah, just goes to show, with time and money EVERYTHING is possible

 

[filo77]

Thanks

Thank you very much for this info.

I finally managed to convince the guy to change the table structure.

You are right, the table design was copied from a spreadsheet, which was creted from a paper form. I have no idea why they approached the problem this way. I was brought on this project later.

I also have 180 fields that go accros. Can you imagine what kind of hell it would have been if that person didn't change his mind.

Thanks anyway. Now that everything is normalized the query is a simple one.


Your input helped me convince this person to change the design, and I also learnt a few work-arounds, that hopefully I would not have to use in other projects.

You guys rock.

filo

 

[The_Doc_Man]

I'll add one more thought. Look at CROSSTAB queries as a way to generate sums or averages or whatever by rows for every distinct entry.