Help needed for nested iif in microsoft access 2010

[pigkachu]

Hi all,

Current i have 3 table known as

Trainee- nric,name,address
Result- nric,course_code,result
Courses- course_code,count_core,count_elective,core/elective

i link Trainee.nric to Result.nric and Result.course_code to Courses.course_code

i need to show the criteria whereby the trainee must met the condition of completing 7 core module and 3 elective in order to be eligible.

im able to sumup the number by using sum(count_core) and sum(count_elective) to show that the trainee met the 7 core and 3 elective criteria.

Full qualified: IIf([Sumofcount_core]>=5 And [Sumofcount_elective]>=3,"Qualified","not qualified")

however i need to add one more condition because those that take the core/elective might fail and i might be still count them as pass so i need to add in [Result].[Result] = "COMPETENT" in my code

Would appreciate alot if someone could help me in that
Thanks.

 

[marlan]

Hi,

I am having difficulties understanding your question...

basically, nesting IIF function, is putting an inner IIF function a result sections an outer IIF function:

IIF(OuterCondition ,IIF(InnerCondition, 2Truse,OuterTrueInnerFalt), OuterFalt)

 

[pigkachu]

Hi,

I am having difficulties understanding your question...

basically, nesting IIF function, is putting an inner IIF function a result sections an outer IIF function:

IIF(OuterCondition ,IIF(InnerCondition, 2Truse,OuterTrueInnerFalt), OuterFalt)

Hi marlan,

Thanks for the reply

i tried nested IIF function

example:
IIf([Sumofcount_core]>=7 And [Sumofcount_elective]>=3,IIF([RESULT].[RESULT]="COMPETENT","Qualified","not qualified"))

but it show me this error

"You tried to execute a query that does not include the specificied expression 'IIf([Sumofcount_core]>=7 And [Sumofcount_elective]>=3,IIF([RESULT].[RESULT]="COMPETENT","Qualified","not qualified"))' as part of an aggregate function"

The code make sense to me though.

 

[marlan]

there is a drop-down list of aggregation functions, try changing it to 'expression'.

 

[pigkachu]

Hi marlan,

It's already in expression mode.

 

[marlan]

Hi,

it seems to me you are missing the False section of the outer IIF.

besides that, if this function should work on the results of the aggregation, try separating the queries: one for aggregating, and then another query, on the aggregation.
I find it could sometimes help if you first save (ctrl+s) the aggregation section of the query, then add the calculated fields.

Break up complexes...

 

[pigkachu]

Hi,

it seems to me you are missing the False section of the outer IIF.

besides that, if this function should work on the results of the aggregation, try separating the queries: one for aggregating, and then another query, on the aggregation.
I find it could sometimes help if you first save (ctrl+s) the aggregation section of the query, then add the calculated fields.

Break up complexes...

Hi can you give me an example? Thanks