Query is "Too Complex" Need help with alternate coding.

I got an error message that the query was “Too Complex”.  When I entered the code for column 68, I got the error message, “The query is too complex”.  I need help with coding to avoid the problem.

This query should contain 110 columns:  5 from the Component table and 105 calculated.

Purpose of the query:  This is for a condominium associations Reserve Fund.  The fund tracks each component: e.g., roof, interior painting, carpets, etc.

The first 5 columns list the component information.

The association prepares a 30year budget which indicates for each component in which year the Cost will be spent based on the estimated life of the component.

The next 15 columns, BY1 to BY15, calculates the year(s) in which the Cost will be spent.

The final 90 columns, CY1 to CY30, show the Cost of each component in the years that Cost is incurred.

NOTE re. columns CY1 to CY30:  I had to divide each year (i.e., CY1 into 3 columns, CY1a, CY1b and CY1 (which added CY1a + CY1b) because the code for one year alone violate the “nesting level limit” of 10.   Consequently, instead of having 30 columns, I now have 90 columns for the 30 years.

When I entered the code for column CY17b, I got the error message, “The query is too complex”.

Here is the coding……………………..

--comment:  first 5 columns

SELECT Component.ComponentID, Component.Component, Component.UsefullLife, Component.RemainingLife, Component.CostEstimated,

--Comment: next 15 columns

RemainingLife+1 AS BY1,

IIf(BY1+UsefullLife*1>30,0,BY1+UsefullLife*1) AS BY2,

IIf(BY1+UsefullLife*2>30,0,BY1+UsefullLife*2) AS BY3,

IIf(BY1+UsefullLife*3>30,0,BY1+UsefullLife*3) AS BY4,

IIf(BY1+UsefullLife*4>30,0,BY1+UsefullLife*4) AS BY5,

IIf(BY1+UsefullLife*5>30,0,BY1+UsefullLife*5) AS BY6,

IIf(BY1+UsefullLife*6>30,0,BY1+UsefullLife*6) AS BY7,

IIf(BY1+UsefullLife*7>30,0,BY1+UsefullLife*7) AS BY8,

IIf(BY1+UsefullLife*8>30,0,BY1+UsefullLife*8) AS BY9,

IIf(BY1+UsefullLife*9>30,0,BY1+UsefullLife*9) AS BY10,

IIf(BY1+UsefullLife*10>30,0,BY1+UsefullLife*10) AS BY11,

IIf(BY1+UsefullLife*11>30,0,BY1+UsefullLife*11) AS BY12,

IIf(BY1+UsefullLife*12>30,0,BY1+UsefullLife*12) AS BY13,

IIf(BY1+UsefullLife*13>30,0,BY1+UsefullLife*13) AS BY14,

IIf(BY1+UsefullLife*14>30,0,BY1+UsefullLife*14) AS BY15,

--Comment: next 90 columns

IIF(1=BY1,CostEstimated, IIF(1=BY2,CostEstimated, IIF(1=BY3,CostEstimated, IIF(1=BY4,CostEstimated, IIF(1=BY5,CostEstimated, IIF(1=BY6,CostEstimated, IIF(1=BY7,CostEstimated, 0))))))))

AS CY1a

IIF(1=BY8,CostEstimated, IIF(1=BY9,CostEstimated, IIF(1=BY10,CostEstimated, IIF(1=BY11,CostEstimated, IIF(1=BY12,CostEstimated, IIF(1=BY13,CostEstimated, IIF(1=BY14,CostEstimated,

IIF(1=BY15,CostEstimated,0))))))))

AS CY1b

CY1a + CY1b  AS CY1

IIF(2=BY1,CostEstimated,IIF(2=BY2,CostEstimated,IIF(2=BY3,CostEstimated,IIF(2=BY4,CostEstimated,IIF(2=BY5,CostEstimated,IIF(2=BY6,CostEstimated,IIF(2=BY7,CostEstimated,0)))))))

AS CY2a

IIF(2=BY8,CostEstimated,IIF(2=BY9,CostEstimated,IIF(2=BY10,CostEstimated,IIF(2=BY11,CostEstimated,IIF(2=BY12,CostEstimated,IIF(2=BY13,CostEstimated,IIF(2=BY14,CostEstimated,

IIF(2=BY15,CostEstimated,0))))))))

AS CY2b

CY2a + CY2b AS CY2

IIF(3=BY1,CostEstimated,IIF(3=BY2,CostEstimated,IIF(3=BY3,CostEstimated,IIF(3=BY4,CostEstimated,IIF(3=BY5,CostEstimated,IIF(3=BY6,CostEstimated,IIF(3=BY7,CostEstimated,0)))))))

AS CY3a

IIF(3=BY8,CostEstimated,IIF(3=BY9,CostEstimated,IIF(3=BY10,CostEstimated,IIF(3=BY11,CostEstimated,IIF(3=BY12,CostEstimated,IIF(3=BY13,CostEstimated,IIF(3=BY14,CostEstimated,

IIF(3=BY15,CostEstimated,0))))))))

AS CY3b

CY3a + CY3b  AS CY3

IIF(4=BY1,CostEstimated,IIF(4=BY2,CostEstimated,IIF(4=BY3,CostEstimated,IIF(4=BY4,CostEstimated,IIF(4=BY5,CostEstimated,IIF(4=BY6,CostEstimated,IIF(4=BY7,CostEstimated,0)))))))

AS CY4a

IIF(4=BY8,CostEstimated,IIF(4=BY9,CostEstimated,IIF(4=BY10,CostEstimated,IIF(4=BY11,CostEstimated,IIF(4=BY12,CostEstimated,IIF(4=BY13,CostEstimated,IIF(4=BY14,CostEstimated,

IIF(4=BY15,CostEstimated,0))))))))

AS CY4b

CY4a + CY4b AS CY4

IIF(5=BY1,CostEstimated,IIF(5=BY2,CostEstimated,IIF(5=BY3,CostEstimated,IIF(5=BY4,CostEstimated,IIF(5=BY5,CostEstimated,IIF(5=BY6,CostEstimated,IIF(5=BY7,CostEstimated,0)))))))

AS CY5a

IIF(5=BY8,CostEstimated,IIF(5=BY9,CostEstimated,IIF(5=BY10,CostEstimated,IIF(5=BY11,CostEstimated,IIF(5=BY12,CostEstimated,IIF(5=BY13,CostEstimated,IIF(5=BY14,CostEstimated,

IIF(5=BY15,CostEstimated,0))))))))

AS CY5b

CY5a + CY5b AS CY5

IIF(6=BY1,CostEstimated,IIF(6=BY2,CostEstimated,IIF(6=BY3,CostEstimated,IIF(6=BY4,CostEstimated,IIF(6=BY5,CostEstimated,IIF(6=BY6,CostEstimated,IIF(6=BY7,CostEstimated,0)))))))

AS CY6a

IIF(6=BY8,CostEstimated,IIF(6=BY9,CostEstimated,IIF(6=BY10,CostEstimated,IIF(6=BY11,CostEstimated,IIF(6=BY12,CostEstimated,IIF(6=BY13,CostEstimated,IIF(6=BY14,CostEstimated,

IIF(6=BY15,CostEstimated,0))))))))

AS CY6b

CY6a + CY6b AS CY6

IIF(7=BY1,CostEstimated,IIF(7=BY2,CostEstimated,IIF(7=BY3,CostEstimated,IIF(7=BY4,CostEstimated,IIF(7=BY5,CostEstimated,IIF(7=BY6,CostEstimated,IIF(7=BY7,CostEstimated,0)))))))

AS CY7a

IIF(7=BY8,CostEstimated,IIF(7=BY9,CostEstimated,IIF(7=BY10,CostEstimated,IIF(7=BY11,CostEstimated,IIF(7=BY12,CostEstimated,IIF(7=BY13,CostEstimated,IIF(7=BY14,CostEstimated,

IIF(7=BY15,CostEstimated,0))))))))

AS CY7b

CY7a + CY7b AS CY7

IIF(8=BY1,CostEstimated,IIF(8=BY2,CostEstimated,IIF(8=BY3,CostEstimated,IIF(8=BY4,CostEstimated,IIF(8=BY5,CostEstimated,IIF(8=BY6,CostEstimated,IIF(8=BY7,CostEstimated,0)))))))

AS CY8a

IIF(8=BY8,CostEstimated,IIF(8=BY9,CostEstimated,IIF(8=BY10,CostEstimated,IIF(8=BY11,CostEstimated,IIF(8=BY12,CostEstimated,IIF(8=BY13,CostEstimated,IIF(8=BY14,CostEstimated,

IIF(8=BY15,CostEstimated,0))))))))

AS CY8b

CY8a + CY8b AS CY8

IIF(9=BY1,CostEstimated,IIF(9=BY2,CostEstimated,IIF(9=BY3,CostEstimated,IIF(9=BY4,CostEstimated,IIF(9=BY5,CostEstimated,IIF(9=BY6,CostEstimated,

IIF(9=BY7,CostEstimated,0)))))))

AS CY9a

IIF(9=BY8,CostEstimated,IIF(9=BY9,CostEstimated,IIF(9=BY10,CostEstimated,IIF(9=BY11,CostEstimated,IIF(9=BY12,CostEstimated,IIF(9=BY13,CostEstimated,IIF(9=BY14,CostEstimated,

IIF(9=BY15,CostEstimated,0))))))))

AS CY9b

CY9a + CY9b AS CY9

IIF(10=BY1,CostEstimated,IIF(10=BY2,CostEstimated,IIF(10=BY3,CostEstimated,IIF(10=BY4,CostEstimated,IIF(10=BY5,CostEstimated,IIF(10=BY6,CostEstimated,

IIF(10=BY7,CostEstimated,0)))))))

AS CY10a

IIF(10=BY8,CostEstimated,IIF(10=BY9,CostEstimated,IIF(10=BY10,CostEstimated,IIF(10=BY11,CostEstimated,IIF(10=BY12,CostEstimated,IIF(10=BY13,CostEstimated,IIF(10=BY14,CostEstimated,

IIF(10=BY15,CostEstimated,0))))))))

AS CY10b

CY10a + CY10b AS CY10

IIF(11=BY1,CostEstimated,IIF(11=BY2,CostEstimated,IIF(11=BY3,CostEstimated,IIF(11=BY4,CostEstimated,IIF(11=BY5,CostEstimated,IIF(11=BY6,CostEstimated,

IIF(11=BY7,CostEstimated,0)))))))

AS CY11a

IIF(11=BY8,CostEstimated,IIF(11=BY9,CostEstimated,IIF(11=BY10,CostEstimated,IIF(11=BY11,CostEstimated,IIF(11=BY12,CostEstimated,IIF(11=BY13,CostEstimated,IIF(11=BY14,CostEstimated,

IIF(11=BY15,CostEstimated,0))))))))

AS CY11b

CY11a + CY11b AS CY11

IIF(12=BY1,CostEstimated,IIF(12=BY2,CostEstimated,IIF(12=BY3,CostEstimated,IIF(12=BY4,CostEstimated,IIF(12=BY5,CostEstimated,IIF(12=BY6,CostEstimated,

IIF(12=BY7,CostEstimated,0)))))))

AS CY12a

IIF(12=BY8,CostEstimated,IIF(12=BY9,CostEstimated,IIF(12=BY10,CostEstimated,IIF(12=BY11,CostEstimated,IIF(12=BY12,CostEstimated,IIF(12=BY13,CostEstimated,IIF(12=BY14,CostEstimated,

IIF(12=BY15,CostEstimated,0))))))))

AS CY12b

CY12a + CY12b AS CY12

IIF(13=BY1,CostEstimated,IIF(13=BY2,CostEstimated,IIF(13=BY3,CostEstimated,IIF(13=BY4,CostEstimated,IIF(13=BY5,CostEstimated,IIF(13=BY6,CostEstimated,IIF(13=BY7,CostEstimated,0)))))))

AS CY13a

IIF(13=BY8,CostEstimated,IIF(13=BY9,CostEstimated,IIF(13=BY10,CostEstimated,IIF(13=BY11,CostEstimated,IIF(13=BY12,CostEstimated,IIF(13=BY13,CostEstimated,IIF(13=BY14,CostEstimated,

IIF(13=BY15,CostEstimated,0))))))))

AS CY13b

CY13a + CY13b AS CY13

IIF(14=BY1,CostEstimated,IIF(14=BY2,CostEstimated,IIF(14=BY3,CostEstimated,IIF(14=BY4,CostEstimated,IIF(14=BY5,CostEstimated,IIF(14=BY6,CostEstimated,IIF(14=BY7,CostEstimated,0)))))))

AS CY14a

IIF(14=BY8,CostEstimated,IIF(14=BY9,CostEstimated,IIF(14=BY10,CostEstimated,IIF(14=BY11,CostEstimated,IIF(14=BY12,CostEstimated,IIF(14=BY13,CostEstimated,IIF(14=BY14,CostEstimated,

IIF(14=BY15,CostEstimated,0))))))))

AS CY14b

CY14a + CY14b AS CY14

IIF(15=BY1,CostEstimated,IIF(15=BY2,CostEstimated,IIF(15=BY3,CostEstimated,IIF(15=BY4,CostEstimated,IIF(15=BY5,CostEstimated,IIF(15=BY6,CostEstimated,IIF(15=BY7,CostEstimated,0)))))))

AS CY15a

IIF(15=BY8,CostEstimated,IIF(15=BY9,CostEstimated,IIF(15=BY10,CostEstimated,IIF(15=BY11,CostEstimated,IIF(15=BY12,CostEstimated,IIF(15=BY13,CostEstimated,IIF(15=BY14,CostEstimated,

IIF(15=BY15,CostEstimated,0))))))))

AS CY15b

CY15a + CY15b AS CY15

IIF(16=BY1,CostEstimated,IIF(16=BY2,CostEstimated,IIF(16=BY3,CostEstimated,IIF(16=BY4,CostEstimated,IIF(16=BY5,CostEstimated,IIF(16=BY6,CostEstimated,IIF(16=BY7,CostEstimated,0)))))))

AS CY16a

IIF(16=BY8,CostEstimated,IIF(16=BY9,CostEstimated,IIF(16=BY10,CostEstimated,IIF(16=BY11,CostEstimated,IIF(16=BY12,CostEstimated,IIF(16=BY13,CostEstimated,IIF(16=BY14,CostEstimated,

IIF(16=BY15,CostEstimated,0))))))))

AS CY16b

CY16a + CY16b AS CY16

IIF(17=BY1,CostEstimated,IIF(17=BY2,CostEstimated,IIF(17=BY3,CostEstimated,IIF(17=BY4,CostEstimated,IIF(17=BY5,CostEstimated,IIF(17=BY6,CostEstimated,IIF(17=BY7,CostEstimated,0)))))))

AS CY17a

IIF(17=BY8,CostEstimated,IIF(17=BY9,CostEstimated,IIF(17=BY10,CostEstimated,IIF(17=BY11,CostEstimated,IIF(17=BY12,CostEstimated,IIF(17=BY13,CostEstimated,IIF(17=BY14,CostEstimated,

IIF(17=BY15,CostEstimated,0))))))))

AS CY17b

CY17a + CY17b AS CY17

IIF(18=BY1,CostEstimated,IIF(18=BY2,CostEstimated,IIF(18=BY3,CostEstimated,IIF(18=BY4,CostEstimated,IIF(18=BY5,CostEstimated,IIF(18=BY6,CostEstimated,IIF(18=BY7,CostEstimated,0)))))))

AS CY18a

IIF(18=BY8,CostEstimated,IIF(18=BY9,CostEstimated,IIF(18=BY10,CostEstimated,IIF(18=BY11,CostEstimated,IIF(18=BY12,CostEstimated,IIF(18=BY13,CostEstimated,IIF(18=BY14,CostEstimated,

IIF(18=BY15,CostEstimated,0))))))))

AS CY18b

CY18a + CY18b AS CY18

IIF(19=BY1,CostEstimated,IIF(19=BY2,CostEstimated,IIF(19=BY3,CostEstimated,IIF(19=BY4,CostEstimated,IIF(19=BY5,CostEstimated,IIF(19=BY6,CostEstimated,IIF(19=BY7,CostEstimated,0)))))))

AS CY19a

IIF(19=BY8,CostEstimated,IIF(19=BY9,CostEstimated,IIF(19=BY10,CostEstimated,IIF(19=BY11,CostEstimated,IIF(19=BY12,CostEstimated,IIF(19=BY13,CostEstimated,IIF(19=BY14,CostEstimated,

IIF(19=BY15,CostEstimated,0))))))))

AS CY19b

CY19a + CY19b AS CY19

IIF(20=BY1,CostEstimated,IIF(20=BY2,CostEstimated,IIF(20=BY3,CostEstimated,IIF(20=BY4,CostEstimated,IIF(20=BY5,CostEstimated,IIF(20=BY6,CostEstimated,IIF(20=BY7,CostEstimated,0)))))))

AS CY20a

IIF(20=BY8,CostEstimated,IIF(20=BY9,CostEstimated,IIF(20=BY10,CostEstimated,IIF(20=BY11,CostEstimated,IIF(20=BY12,CostEstimated,IIF(20=BY13,CostEstimated,IIF(20=BY14,CostEstimated,

IIF(20=BY15,CostEstimated,0))))))))

AS CY20b

CY20a + CY20b AS CY20

IIF(21=BY1,CostEstimated,IIF(21=BY2,CostEstimated,IIF(21=BY3,CostEstimated,IIF(21=BY4,CostEstimated,IIF(21=BY5,CostEstimated,IIF(21=BY6,CostEstimated,IIF(21=BY7,CostEstimated,0)))))))

AS CY21a

IIF(21=BY8,CostEstimated,IIF(21=BY9,CostEstimated,IIF(21=BY10,CostEstimated,IIF(21=BY11,CostEstimated,IIF(21=BY12,CostEstimated,IIF(21=BY13,CostEstimated,IIF(21=BY14,CostEstimated,

IIF(21=BY15,CostEstimated,0))))))))

AS CY21b

CY21a + CY21b AS CY21

IIF(22=BY1,CostEstimated,IIF(22=BY2,CostEstimated,IIF(22=BY3,CostEstimated,IIF(22=BY4,CostEstimated,IIF(22=BY5,CostEstimated,IIF(22=BY6,CostEstimated,IIF(22=BY7,CostEstimated,0)))))))

AS CY22a

IIF(22=BY8,CostEstimated,IIF(22=BY9,CostEstimated,IIF(22=BY10,CostEstimated,IIF(22=BY11,CostEstimated,IIF(22=BY12,CostEstimated,IIF(22=BY13,CostEstimated,IIF(22=BY15,CostEstimated,0))))))))

AS CY22b

CY22a + CY22b AS CY22

IIF(23=BY1,CostEstimated,IIF(23=BY2,CostEstimated,IIF(23=BY3,CostEstimated,IIF(23=BY4,CostEstimated,IIF(23=BY5,CostEstimated,IIF(23=BY6,CostEstimated,IIF(23=BY7,CostEstimated,0)))))))

AS CY23a

IIF(23=BY8,CostEstimated,IIF(23=BY9,CostEstimated,IIF(23=BY10,CostEstimated,IIF(23=BY11,CostEstimated,IIF(23=BY12,CostEstimated,IIF(23=BY13,CostEstimated,IIF(23=BY14,CostEstimated,

IIF(23=BY15,CostEstimated,0))))))))

AS CY23b

CY23a + CY23b AS CY23

IIF(24=BY1,CostEstimated,IIF(24=BY2,CostEstimated,IIF(24=BY3,CostEstimated,IIF(24=BY4,CostEstimated,IIF(24=BY5,CostEstimated,IIF(24=BY6,CostEstimated,IIF(24=BY7,CostEstimated,0)))))))

AS CY24a

IIF(24=BY8,CostEstimated,IIF(24=BY9,CostEstimated,IIF(24=BY10,CostEstimated,IIF(24=BY11,CostEstimated,IIF(24=BY12,CostEstimated,IIF(24=BY13,CostEstimated,IIF(24=BY14,CostEstimated,

IIF(24=BY15,CostEstimated,0))))))))

AS CY24b

CY24a + CY24b AS CY24

IIF(25=BY1,CostEstimated,IIF(25=BY2,CostEstimated,IIF(25=BY3,CostEstimated,IIF(25=BY4,CostEstimated,IIF(25=BY5,CostEstimated,IIF(25=BY6,CostEstimated,IIF(25=BY7,CostEstimated,0)))))))

AS CY25a

IIF(25=BY8,CostEstimated,IIF(25=BY9,CostEstimated,IIF(25=BY10,CostEstimated,IIF(25=BY11,CostEstimated,IIF(25=BY12,CostEstimated,IIF(25=BY13,CostEstimated,IIF(25=BY14,CostEstimated,

IIF(25=BY15,CostEstimated,0))))))))

AS CY25b

CY25a + CY25b AS CY25

IIF(26=BY1,CostEstimated,IIF(26=BY2,CostEstimated,IIF(26=BY3,CostEstimated,IIF(26=BY4,CostEstimated,IIF(26=BY5,CostEstimated,IIF(26=BY6,CostEstimated,IIF(26=BY7,CostEstimated,0)))))))

AS CY26a

IIF(26=BY8,CostEstimated,IIF(26=BY9,CostEstimated,IIF(26=BY10,CostEstimated,IIF(26=BY11,CostEstimated,IIF(26=BY12,CostEstimated,IIF(26=BY13,CostEstimated,IIF(26=BY14,CostEstimated,

IIF(26=BY15,CostEstimated,0))))))))

AS CY26b

CY26a + CY26b AS CY26

IIF(27=BY1,CostEstimated,IIF(27=BY2,CostEstimated,IIF(27=BY3,CostEstimated,IIF(27=BY4,CostEstimated,IIF(27=BY5,CostEstimated,IIF(27=BY6,CostEstimated,IIF(27=BY7,CostEstimated,0)))))))

AS CY27a

IIF(27=BY8,CostEstimated,IIF(27=BY9,CostEstimated,IIF(27=BY10,CostEstimated,IIF(27=BY11,CostEstimated,IIF(27=BY12,CostEstimated,IIF(27=BY13,CostEstimated,IIF(27=BY14,CostEstimated,

IIF(27=BY15,CostEstimated,0))))))))

AS CY27b

CY27a + CY27b AS CY27

IIF(28=BY1,CostEstimated,IIF(28=BY2,CostEstimated,IIF(28=BY3,CostEstimated,IIF(28=BY4,CostEstimated,IIF(28=BY5,CostEstimated,IIF(28=BY6,CostEstimated,IIF(28=BY7,CostEstimated,0)))))))

AS CY28a

IIF(28=BY8,CostEstimated,IIF(28=BY9,CostEstimated,IIF(28=BY10,CostEstimated,IIF(28=BY11,CostEstimated,IIF(28=BY12,CostEstimated,IIF(28=BY13,CostEstimated,IIF(28=BY14,CostEstimated,

IIF(28=BY15,CostEstimated,0))))))))

AS CY28b

CY28a + CY28b AS CY28

IIF(29=BY1,CostEstimated,IIF(29=BY2,CostEstimated,IIF(29=BY3,CostEstimated,IIF(29=BY4,CostEstimated,IIF(29=BY5,CostEstimated,IIF(29=BY6,CostEstimated,IIF(29=BY7,CostEstimated,0)))))))

AS CY29a

IIF(29=BY8,CostEstimated,IIF(29=BY9,CostEstimated,IIF(29=BY10,CostEstimated,IIF(29=BY11,CostEstimated,IIF(29=BY12,CostEstimated,IIF(29=BY13,CostEstimated,IIF(29=BY14,CostEstimated,

IIF(29=BY15,CostEstimated,0))))))))

AS CY29b

CY29a + CY29b AS CY29

IIF(30=BY1,CostEstimated,IIF(30=BY2,CostEstimated,IIF(30=BY3,CostEstimated,IIF(30=BY4,CostEstimated,IIF(30=BY5,CostEstimated,IIF(30=BY6,CostEstimated,IIF(30=BY7,CostEstimated,0)))))))

AS CY30a

IIF(30=BY8,CostEstimated,IIF(30=BY9,CostEstimated,IIF(30=BY10,CostEstimated,IIF(30=BY11,CostEstimated,IIF(30=BY12,CostEstimated,IIF(30=BY13,CostEstimated,IIF(30=BY14,CostEstimated,

IIF(30=BY15,CostEstimated,0))))))))

AS CY30b

CY30a + CY30b AS CY30

--Comment: final code

FROM Component;