query too complex
I have created a query which sets a target per quarter after evaluating the value (eg: [V2000q4]) and target (eg: [Target 2000q4])for the previous quarter and then adding any additional transactions (eg: [T2001q1]). ie:
IIf([V2000q4]>[Target2000q4],Nz([V2000q4],0)+Nz([T2001q1],0),Nz([Target2000q4],0)+Nz([T2001q1],0)) AS Target2001q1,
IIf([V2001q1]>[Target2001q1],Nz([V2001q1],0)+Nz([T2001q2],0),Nz([Target2001q1],0)+Nz([T2001q2],0)) AS Target2001q2,
IIf([V2001q2]>[Target2001q2],Nz([V2001q2],0)+Nz([T2001q3],0),Nz([Target2001q2],0)+Nz([T2001q3],0)) AS Target2001q3,
The query works fine until I repeat the above expression too many times, then I get an error message saying "query too complex". I am sure that if I wrote this as code it would work, but I am not sure how to write it. Can anyone help?