Query error message: "Too Complex"

[malcolm_p_galvin_jr]

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." See attached message.
• 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.  See attached message.
• I then used SSMS again with a shortened query:
  • SELECT ComponentID, GeneralLedgerAcct, Component, CatDescription, FirstYear, UsefullLife, RemainingLife, CostEstimated, RemainingLife + 1 AS BY1, BY1 + 1 AS BY2
    FROM dbo.Component
  • 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'
  • SELECT ComponentID, GeneralLedgerAcct, Component, CatDescription, FirstYear, UsefullLife, RemainingLife, CostEstimated, RemainingLife + 1 AS BY1, 'BY1' + 1 AS BY2
    FROM dbo.Component
  • 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.

Please help!!!!

[#2]

@malcolm_p_galvin_jr 

I received code that address the issue in SQLExpresss (which follows). Would anyone know the Access SQL version of the following code?
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),
BY15 = iif( BY1 + UsefullLife * 14 > 30, 0, BY1 + UsefullLife * 14)
from Component
cross apply (values ([RemainingLife] + 1)) t(BY1)

[#3 · Accepted solution]

@malcolm_p_galvin_jr 

you can use a User-Defined-Function in your query (ms access) so you won't get the "Too Complex".

error. see Module1 for the function.

Open Query1 in design view and see how this function is being called.

[#4]

Thank you!!