IIf using other field values in same query

[ErikaMarie0421]

Greetings. I am excited about joining this forum! I love learning more about Access!

Anyway - I am in the process of building a query that will calculate tuition for attendees that come in for our seminars. The tuition levels are decided by a few factors, the main factor being personal production by the agent. There are three levels of production minimums, and the tuition will either be $0, $100 or $200 based on what they have produced. These production requirements and tuition levels are stored in a table in Access based on a seminar code (TBL_SeminarProductionRequirements_LOOKUP), the production, however, is calculated in my query from a few different places. I was able to build the expression that calculates the production total, but now I am trying to write a IIf, Or, IIf Between....Or IIf Between...Then bla bla and I can't figure out what I am missing. I am not too familiar yet with SQL, so I do all my building in design view, so here is my expression as of now (which is not working):

Tuition Based on Prev Yr: IIf([QRY_SeminarInvitations_1]![Total Prod_Prev Yr]>=[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A PRODUCTION MINIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A TUITION],IIf([QRY_SeminarInvitations_1]![Total Prod_Prev Yr] Between [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MINIMUM] And [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MAXIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B TUITION],IIf([QRY_SeminarInvitations_1]![Total Prod_Prev Yr] Between [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MINIMUM] And [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MAXIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C TUITION],"Does Not Qualify")))

I also tried this (instead of using "Between"):

Tuition Based on Prev Yr: IIf([QRY_SeminarInvitations_1]![Total Prod_Prev Yr]>=[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A PRODUCTION MINIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1A TUITION],IIf([TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MAXIMUM]>=[QRY_SeminarInvitations_1]![Total Prod_Prev Yr] >= [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B PRODUCTION MINIMUM], [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1B TUITION],IIf([TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MAXIMUM]>=[QRY_SeminarInvitations_1]![Total Prod_Prev Yr] >= [TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C PRODUCTION MINIMUM],[TBL_SeminarProductionRequirements_LOOKUP]![TYPE 1C TUITION],"Does Not Qualify")))

Are you able to tell from what I provided what I might be doing wrong?

Thanks so much!


Would it be too much for me to include on here the SQL for the entire query where I would like to calculate this tuition? Here it is:



SELECT TBL_SeminarDates_LOOKUP.[Seminar Code], DB2ADMIN_FDO_SEMINAR.AGT_ID, RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_LAST_NAME]) & ', ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_FIRST_NAME]) & ' ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_MIDDLE_NAME]) AS [Agent Name], DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM, DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.GA_LAST_NA ME, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_REG_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CAR_EFF_DA TE, QRY_LengthOfContract.[Length of Contract], QRY_AgtContDate_MonthsActive_CurrYr.[Months Active in Curr Yr] AS [Months Active in Curr Year], [QRY_AgtNAFYC_CurrYr]![Curr Yr NAFYC] AS [Curr Year NAFYC], [QRY_AgtNAFYC_CurrYr]![Curr Yr Cases] AS [Curr Year Cases], [QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr] AS [Months Active in Prev Year], [QRY_AgtNAFYC_PrevYr]![Prev Yr NAFYC] AS [Prev Year NAFYC], [QRY_AgtNAFYC_PrevYr]![Prev Yr Cases] AS [Prev Year Cases], Nz(Sum([QRY_AgtContDate_MonthsActive_CurrYr]![Months Active in Curr Yr]+[QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr]),0) AS [Total Months Active (Prev & Curr Yr)], Sum(Nz([Curr Year NAFYC],0)+Nz([Prev Year NAFYC],0)) AS [Total NAFYC], IIf([Total Months Active (Prev & Curr Yr)]=0,0,[Total NAFYC]/[Total Months Active (Prev & Curr Yr)]) AS [Monthly NAFYC Average], IIf([Months Active in Curr Year]=0,0,[Curr Year NAFYC]/[Months Active in Curr Year]) AS [Curr Yr Monthly NAFYC Avg], [TBL_Production_NonProp-ONESCO_2010].[ONESCO GDC (Excluding Non-Prop)] AS [Curr Yr ONESCO Net GDC], [TBL_Production_NonProp-ONESCO_2010].[50% Non-Prop] AS [Curr Yr 50% Non-Prop], [TBL_Production_NonProp-ONESCO_2009].[ONESCO GDC (Excluding Non-Prop)] AS [Prev Yr ONESCO Net GDC], [TBL_Production_NonProp-ONESCO_2009].[50% Non-Prop] AS [Prev Yr 50% Non-Prop], IIf([Months Active in Curr Year]=0,0,[Curr Yr ONESCO Net GDC]/[Months Active in Curr Year]) AS [Curr Yr Monthly ONESCO Avg], IIf([Months Active in Curr Year]=0,0,[Curr Yr 50% Non-Prop]/[Months Active in Curr Year]) AS [Curr Yr Monthly Non-Prop Avg], IIf([Curr Yr Monthly ONESCO Avg]>=[Curr Yr MONTHLY Maximum ONESCO for Type 1],[Curr Yr MONTHLY Maximum ONESCO for Type 1],[Curr Yr Monthly ONESCO Avg]) AS [Curr Yr ONESCO Included (Monthly Avg)], IIf([Prev Yr ONESCO Net GDC]>=[Prev Yr Maximum ONESCO for Type 1],[Prev Yr Maximum ONESCO for Type 1],[Prev Yr ONESCO Net GDC]) AS [Prev Year ONESCO Included], Sum(Nz([Prev Year NAFYC],0)+Nz([Prev Yr 50% Non-Prop],0)+Nz([Prev Year ONESCO Included],0)) AS [Total Prod_Prev Yr], Sum(Nz([Curr Yr Monthly NAFYC Avg],0)+Nz([Curr Yr Monthly Non-Prop Avg])+Nz([Curr Yr ONESCO Included (Monthly Avg)],0)) AS [Avg Prod_Curr Yr]
FROM (((((((((((TBL_SeminarDates_LOOKUP LEFT JOIN DB2ADMIN_FDO_SEMINAR ON TBL_SeminarDates_LOOKUP.[Seminar Name] = DB2ADMIN_FDO_SEMINAR.SEMINAR_NAME) LEFT JOIN DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION ON DB2ADMIN_FDO_SEMINAR.AGT_ID = DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_ID) LEFT JOIN QRY_AgtNAFYC_CurrYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtNAFYC_CurrYr.AGT_ID) LEFT JOIN QRY_AgtNAFYC_PrevYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtNAFYC_PrevYr.AGT_ID) LEFT JOIN QRY_AgtContDate_MonthsActive_CurrYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtContDate_MonthsActive_CurrYr.AGT_ID) LEFT JOIN QRY_AgtContDate_MonthsActive_PrevYr ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_AgtContDate_MonthsActive_PrevYr.AGT_ID) LEFT JOIN [TBL_Production_NonProp-ONESCO_2010] ON DB2ADMIN_FDO_SEMINAR.AGT_ID = [TBL_Production_NonProp-ONESCO_2010].AGT_ID) LEFT JOIN [TBL_Production_NonProp-ONESCO_2009] ON DB2ADMIN_FDO_SEMINAR.AGT_ID = [TBL_Production_NonProp-ONESCO_2009].AGT_ID) LEFT JOIN DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION ON DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM = DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.WR_CD_AGY_ NUM) LEFT JOIN QRY_LengthOfContract ON DB2ADMIN_FDO_SEMINAR.AGT_ID = QRY_LengthOfContract.PRIMARY_AGT_ID) LEFT JOIN TBL_SeminarProductionRequirements_LOOKUP ON TBL_SeminarDates_LOOKUP.[Seminar Code] = TBL_SeminarProductionRequirements_LOOKUP.[SEMINAR CODE]) LEFT JOIN QRY_SeminarTuition_MaxONESCOConsideration ON TBL_SeminarDates_LOOKUP.[Seminar Code] = QRY_SeminarTuition_MaxONESCOConsideration.[SEMINAR CODE]
WHERE (((DB2ADMIN_FDO_SEMINAR.SEMINAR_STATUS_CD)="1") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_CON T_STAT_CD)="ACTIVE") AND ((DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE _CODE)="CAREER" Or (DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE_ CODE)="BUILDER"))
GROUP BY TBL_SeminarDates_LOOKUP.[Seminar Code], DB2ADMIN_FDO_SEMINAR.AGT_ID, RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_LAST_NAME]) & ', ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_FIRST_NAME]) & ' ' & RTrim([DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION]![AGT_MIDDLE_NAME]), DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_TYPE_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_WR_CD _AGY_NUM, DB2ADMIN_AIS_AGENCY_GENERAL_INFORMATION.GA_LAST_NA ME, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CURR_REG_C ODE, DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.CAR_EFF_DA TE, QRY_LengthOfContract.[Length of Contract], QRY_AgtContDate_MonthsActive_CurrYr.[Months Active in Curr Yr], [QRY_AgtNAFYC_CurrYr]![Curr Yr NAFYC], [QRY_AgtNAFYC_CurrYr]![Curr Yr Cases], [QRY_AgtContDate_MonthsActive_PrevYr]![Months Active in Prev Yr], [QRY_AgtNAFYC_PrevYr]![Prev Yr NAFYC], [QRY_AgtNAFYC_PrevYr]![Prev Yr Cases], [TBL_Production_NonProp-ONESCO_2010].[ONESCO GDC (Excluding Non-Prop)], [TBL_Production_NonProp-ONESCO_2010].[50% Non-Prop], [TBL_Production_NonProp-ONESCO_2009].[ONESCO GDC (Excluding Non-Prop)], [TBL_Production_NonProp-ONESCO_2009].[50% Non-Prop], QRY_SeminarTuition_MaxONESCOConsideration.[Prev Yr Maximum ONESCO for Type 1], QRY_SeminarTuition_MaxONESCOConsideration.[Curr Yr MONTHLY Maximum ONESCO for Type 1], DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_LAST_N AME, TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1A PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1A TUITION], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B PRODUCTION MAXIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1B TUITION], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C PRODUCTION MINIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C PRODUCTION MAXIMUM], TBL_SeminarProductionRequirements_LOOKUP.[TYPE 1C TUITION]
HAVING (((TBL_SeminarDates_LOOKUP.[Seminar Code])=[Enter Seminar Code]))
ORDER BY DB2ADMIN_AIS_AGENTS_GENERAL_INFORMATION.AGT_LAST_N AME;


Thanks again!

[TheShabz]

holy wall of text O_O
tldr

am interested to see the replies.

[ErikaMarie0421]

aaaah.....thought you had a solution for me!
Oh well.....at least your reply made me laugh!
Anyway - if I don't get any suggested solutions, I found a 'back-door' way of calculating. Just broke it into several columns by writing a few extra expressions. Just thought I could simplify it.
Have a good day.