Query error message: "Too Complex"

Question

Here is the query that works without causing the "Too Complex" error message:

--COMMENT: listing the columns of the Component table
SELECT Component.ComponentID, Component.GeneralLedgerAcct, Component.Component, Component.CatDescription, DatePart("yyyy",[FirstYear]) AS [First Year], Component.UsefullLife, Component.RemainingLife, Component.CostEstimated,

--COMMENT: creating calculated columns: BY1 to BY9
[remaininglife]+1 AS BY1,
IIf([BY1]+[UsefullLife]>30,0,[BY1]+[UsefullLife]) AS BY2,
IIf([BY2]=0,0,IIF([BY2]+[UsefullLife]>30,0,[BY2]+[UsefullLife])) AS BY3,
IIf([BY3]=0,0,IIF([BY3]+[UsefullLife]>30,0,[BY3]+[UsefullLife])) AS BY4,
IIf([BY4]=0,0,IIF([BY4]+[UsefullLife]>30,0,[BY4]+[UsefullLife])) AS BY5,
IIf([BY5]=0,0,IIF([BY5]+[UsefullLife]>30,0,[BY5]+[UsefullLife])) AS BY6,
IIf([BY6]=0,0,IIF([BY6]+[UsefullLife]>30,0,[BY6]+[UsefullLife])) AS BY7,
IIf([BY7]=0,0,IIF([BY7]+[UsefullLife]>30,0,[BY7]+[UsefullLife])) AS BY8,
IIf([BY8]=0,0,IIF([BY8]+[UsefullLife]>30,0,[BY8]+[UsefullLife])) AS BY9

FROM Component

-- See attached copy of the executed query.

ISSUE CAUSING THE ERROR MESSAGE:
The above code creates 9 calculated columns. I need 6 more calculated columns, BUT when I try to insert any of the following lines of code, I get the error message "The Query is Too Complex":

IIf([BY9]=0,0,IIF([BY9]+[UsefullLife]>30,0,[BY9]+[UsefullLife])) AS BY10,
IIf([BY10]=0,0,IIF([BY10]+[UsefullLife]>30,0,[BY10]+[UsefullLife])) AS BY11,
IIf([BY11]=0,0,IIF([BY11]+[UsefullLife]>30,0,[BY11]+[UsefullLife])) AS BY12,
IIf([BY12]=0,0,IIF([BY12]+[UsefullLife]>30,0,[BY12]+[UsefullLife])) AS BY13,
IIf([BY13]=0,0,IIF([BY13]+[UsefullLife]>30,0,[BY13]+[UsefullLife])) AS BY14,
IIf([BY14]=0,0,IIF([BY14]+[UsefullLife]>30,0,[BY14]+[UsefullLife])) AS BY15

****This appears to me to be a very simple query .... which is anything but complex.
Please help!!!!****

Things I have tried:
• I tried migrating the database to SQL Express, thinking that the full query might work in there vs MS Access. When I used SSMA for the migration, the query would not transfer. The error message was "Conversion of expression with self-references is not supported."
• I then tried to run the query directly with SSMS. The query would not execute and generated the error messages "Invalid column name 'BY1' ", and so on for each BYx column.
• I then used SSMS again with a shortened query:
o SELECT ComponentID, GeneralLedgerAcct, Component, CatDescription, FirstYear, UsefullLife, RemainingLife, CostEstimated, RemainingLife + 1 AS BY1, BY1 + 1 AS BY2
FROM dbo.Component
o This failed with the following error message, "Invalid column name 'BY1'
• I then ran the above query again, but enclosed BY1 in as follows 'BY1'
o SELECT ComponentID, GeneralLedgerAcct, Component, CatDescription, FirstYear, UsefullLife, RemainingLife, CostEstimated, RemainingLife + 1 AS BY1, 'BY1' + 1 AS BY2
FROM dbo.Component
o This failed with the following error message, "Conversion failed when converting the varchar value 'BY1' to data type int.
• I tried cast() and convert() functions in SSMS, without any success.

Based on all of the above, I am thinking that data types are the issue; a battle between varchar column headings and column row int.

Answer 1

In case of SQL Server, try a query like this:

select *,   
    BY2 = iif( BY1 + UsefullLife * 1 > 30, 0, BY1 + UsefullLife * 1),  
    BY3 = iif( BY1 + UsefullLife * 2 > 30, 0, BY1 + UsefullLife * 2),  
    BY4 = iif( BY1 + UsefullLife * 3 > 30, 0, BY1 + UsefullLife * 3),  
    BY5 = iif( BY1 + UsefullLife * 4 > 30, 0, BY1 + UsefullLife * 4),  
    BY6 = iif( BY1 + UsefullLife * 5 > 30, 0, BY1 + UsefullLife * 5),  
    BY7 = iif( BY1 + UsefullLife * 6 > 30, 0, BY1 + UsefullLife * 6),  
    BY8 = iif( BY1 + UsefullLife * 7 > 30, 0, BY1 + UsefullLife * 7),  
    BY9 = iif( BY1 + UsefullLife * 8 > 30, 0, BY1 + UsefullLife * 8),  
    BY10 = iif( BY1 + UsefullLife * 9 > 30, 0, BY1 + UsefullLife * 9),  
    BY11 = iif( BY1 + UsefullLife * 10 > 30, 0, BY1 + UsefullLife * 10),  
    BY12 = iif( BY1 + UsefullLife * 11 > 30, 0, BY1 + UsefullLife * 11),  
    BY13 = iif( BY1 + UsefullLife * 12 > 30, 0, BY1 + UsefullLife * 12),  
    BY14 = iif( BY1 + UsefullLife * 13 > 30, 0, BY1 + UsefullLife * 13)  
from Component  
cross apply (values ([RemainingLife] + 1)) t(BY1)  

Maybe it can be adjusted for Access too.

Answer 2

Viorel-1

Just opened the query in design view and got the attached error message.
The query still works, even with the error message.