Full Version: Calculation In Query...
razno
I need to incomporate formula for grade for certificate.

So i have have 5 ratings from 0-20.

Insufficient (1)
sufficient (2)
good (3)
verygood (4)
excellet (5)

So formula is if any of 5 ratings are below 10 = its insufficient (1).
Next i must calculate sum off all ratings for all students (i supose using query Total: a+b+c+d+e).

Now, i will have a table with number of points needed for grades. (must incorporate it in database so user can input that numbers… ie 2= from 20-40 pts. (that is for now, later i will have will have to incorporate table calculation in database).

So, how to incorporate this in database so end result can be previewed in datasheet and also also for printing certificates?

Thanks you…

ScottGem
I'm not following your calcs, but if its a basis of x grade = y points, you just add calculated columns to a query.
razno
can you gave me example what to input to and best way to incorporate user entered scale of point (separate table for that?).

ScottGem
Like I said I'm not following your calcs

Also this depends on your table structure.

As far as incorporating user inputs, do these need to be stored or are they ad hoc? ad hoc input could just be entered into a form and referenced in your expressions using the syntax:

=Forms!formname!controlname

An example iof an expression in a query could be:

Rating= IIF(a<10 OR v <10 OR c<10 OR d< 10 OR e<10,,1,2)

There are many other possibilities if you can explain your table structure and calcs more.
razno
Thanks you.

I my query i have points in 5 fields, let name it F1,F2,F3,F4,F5 and have calulated fields Ukupno (sum of all fields).

So now i have Expr1: IIf([Ukupno]>=90;"A";IIf([Ukupno]>=80;"B";IIf([Ukupno]>=70;"C";IIf([Ukupno]>=60;"D";"F"))))

I need to incorporate if any field (f1-5) is <10 grade would be F.
Later i would incorporate user defined fields instead numbers .. i suppose that would be IIf([Ukupno]>=[userdefinedfieldname];.... ?
ScottGem
I would use the SWITCH function instead of nested IIFs.
razno
Switch function..?

All, or only F1-F2 and then Iif based on switch result?

i could not get it to work.

Switch: Switch([razumljevanje]<10;"nedovoljan (1)";[GramatičkeStrukture]<10;"nedovoljan (1)";[Jezičnestrukture]<10;"nedovoljan (1)";[Pisanje]<10;"nedovoljan (1)";[GovornaProdukcija]<10;"nedovoljan (1)";[ukupno]>20;"dovoljan (2)";[ukupno]>40;"dobar (3)";[ukupno]>60;"vrlo dobar(4)";[ukupno]>80;"odličan (5)")

Its Display "nedovoljan (1)" if any of 5 fields are <10, and display only first switch for sum [ukupno].

Maybe to base secondfield on like before, but using iif ([switch]="";.... ?

EDIT:

its seem i get it to work.
Could you please verify if it sound good to you.

Expr1: IIf([Switch]="nedovoljan (1)";"nedovoljan(1)";IIf([Ukupno]>=85;"odličan (5)";IIf([Ukupno]>=75;"vrlodobar (4)";IIf([Ukupno]>=65;"dobar (3)";IIf([Ukupno]>=55;"dovoljan (2)";"nedovoljan (1)")))))
razno
ok, how to filter query based on value of calculated field?

So, basicly i want to filter out if calculated field is "nedovoljan (1)" so that record doesnt show up in reports...

Edit:
I tried query on query and it works... is there any downside to doing this?