Expression too complex - subquery sum not allowed

[FrozenMana]

I need to make changes to expressions in a query that is currently up and running. However the data that needs to be summed in the expressions has grown to the point where access gives the error message that the expression is too complex (see full expression in first code set).

To work around this I have tried to break the data out into smaller groups and then sum the total(see second set of code). However when I attempted that I recevied the error the subqry within cannot be used to sum data.

I have tried to create a sub query to sum the experessions in smaller sections and add the query to the main query however it renders the information drawn to be too large to finish the query.

Exception Pay: Sum(IIf([ExpenseType]="Fees" And [ExpenseDescription]="SFEE",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="SFMN",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="RFMN",[Dollars], IIf([ExpenseType]="Fees" And [ExpenseDescription]="BULK",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="MNM",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="MINM",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="SFEE",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="SFMN",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="RFMN",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="BULK",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="MNM",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="MINM",[Dollars]IIf([ExpenseType]="Fees" And [ExpenseDescription]="LVB",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="ADJ",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="DLX",[Dollars], IIf([ExpenseType]="Fees" And [ExpenseDescription]="DINC",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="NPAY",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="TREC",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="LVB",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="ADJ",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="DLX",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="DINC",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="NAPY",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="TREC",[Dollars],0)))))))))))))))))))))))))

Exception Pay : Sum([EP1]+[EP2])

EP1: Sum(IIf([ExpenseType]="Fees" And [ExpenseDescription]="SFEE",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="SFMN",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="RFMN",[Dollars], IIf([ExpenseType]="Fees" And [ExpenseDescription]="BULK",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="MNM",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="MINM",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="SFEE",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="SFMN",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="RFMN",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="BULK",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="MNM",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="MINM",[Dollars],0)))))))))))))

EP2: Sum(IIf([ExpenseType]="Fees" And [ExpenseDescription]="LVB",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="ADJ",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="DLX",[Dollars], IIf([ExpenseType]="Fees" And [ExpenseDescription]="DINC",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="NPAY",[Dollars],IIf([ExpenseType]="Fees" And [ExpenseDescription]="TREC",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="LVB",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="ADJ",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="DLX",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="DINC",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="NAPY",[Dollars],IIf([ExpenseType]="Fees-MT" And [ExpenseDescription]="TREC",[Dollars],0)))))))))))))

 

[CJ_London]

This is a simpler way of saying the same thing

Exception Pay: Sum(iif([ExpensesType] in ("Fees","Fees-MT") AND [ExpenseDescription] in ("SFEE","SFMN","RFMN" "BULK","MNM","MINM","LVB","ADJ","DLX","DINC","NPAY","TREC"),[Dollars],0))

revised to include EP2

 

[FrozenMana]

That makes a lot more sense. Thank you very much. Something I probably should have noticed myself.