Dynamic Dividing

[easten]

firstly, i would like to thanks for all the answers in this forums, it helps me a lot.

i have a question that i didn't find in this forum.
i have 5 fields (math, physic, informatic, German, and Englih) in one table, and i want to calculate the average's mark from these 5 fields, for example (tbl1.math + tbl1.physic + tbl1.informatic + tbl1.German + tbl1.English)/ 5)
but i want to make the dividing number dynamic, so if the user only inputing math and physic it will automaticly divide with two and not five, and if only math, informatic , German it will be divided with 3, etc.
i can make it in the module but not in the query
i hope i could get the answer

 

[AlexCuse]

I think this is a job for a function anyway. If you must do it in a query, this might work for you (assumes the default is zero and not tested):

(tbl1.math + tbl1.physic + tbl1.informatic + tbl1.German + tbl1.English)/(iif(tbl1.math=0, 0,1) + iif(tbl1.physic=0, 0, 1) + iif(tbl1.informatic=0, 0, 1) + iif(tbl1.German=0, 0,1) + iif(tbl1.English=0,0,1))

You will of course need to add a check that your 'dynamic division number' is not zero before trying to divide.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[easten]

thks Alex
the code really help me a lot

 

[AlexCuse]

Glad it worked for you

Ignorance of certain subjects is a great part of wisdom

 

[lespaul]

another way:

create a query that normalizes your data:

SELECT IDField, OtherIdentifiers, "math" As Course, math As Score FROM TableName WHERE Math <> ""
UNION
SELECT IDField, OtherIdentifiers, "physic", physic FROM TableName WHERE physic <> ""
UNION
SELECT IDField, OtherIdentifiers, "informatic", informatic FROM TableName WHERE informatic <> ""
UNION
SELECT IDField, OtherIdentifiers, "German", German FROM TableName WHERE German <> ""
UNION
SELECT IDField, OtherIdentifiers, "English", English FROM TableName WHERE English <> ""

Then another query:

SELECT IDField, Count(course), Avg(score) FROM qryNormalize GROUP BY IDField

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[MichaelRed]

then again, to perhaps not need to recode, search these (Tek-Tips) fora for basAvg (maybe just "avg(". Along the way, see some interesting new use for Parameter Array ... or perhaps just look htat up via hte ubiquitous F1 ...




MichaelRed