IIF statement too complex, table instead?

[Lauren1989]

I am still pretty new to Access and am stuck.



I have one query that calculates clients' Age This Year by subtracting DOB from current year.

Then I have a second query pulling the calculated Age This Year from the first query. I entered the following IIF statement in a field box of the second query but am getting 'too complex' error. (It works fine up to ten or so).

Factor: IIf([Age This Year]=70,"27.4",iif([Age This Year]=71,"26.5",iif([Age This Year]=72,"25.6",iif([Age This Year]=73,"24.7",iif([Age This Year]=74,"23.8",iif([Age This Year]=75,"22.9",iif([Age This Year]=76,"22",iif([Age This Year]=77,"21.2",iif([Age This Year]=78,"20.3",iif([Age This Year]=79,"19.5",iif([Age This Year]=80,"18.7",iif([Age This Year]=81,"17.9",iif([Age This Year]=82,"17.1",iif([Age This Year]=83,"16.3",iif([Age This Year]=84,"15.5",iif([Age This Year]=85,"14.8",iif([Age This Year]=86,"14.1",iif([Age This Year]=87,"13.4",iif([Age This Year]=88,"12.7",iif([Age This Year]=89,"12",iif([Age This Year]=90,"11.4",iif([Age This Year]=91,"10.8",iif([Age This Year]=92,"10.2",iif([Age This Year]=93,"9.6",iif([Age This Year]=94,"9.1",iif([Age This Year]=95,"8.6",iif([Age This Year]=96,"8.1",iif([Age This Year]=97,"7.6",iif([Age This Year]=98,"7.1",iif([Age This Year]=99,"6.7",iif([Age This Year]=100,"6.3")))))))))))))))))))))))))))))))) 

Fun stuff, I know. Obviously what I'm trying to get is for it to return the IRS factor based on the Age This Year. I've spent a few hours reading around and it seems like people suggest putting a table together. So I made a new table (RMDFactor) that has fields Age and IRS Factor. The question is, how do I get the query to pull the corresponding IRS Factor for the Age This Year in the query?

Any help would be appreciated!

Edit to add: I tried it with switch instead of iif and it didn't work.

[pbaldy]

You would join the new table to the table/query containing the age field on the age field. That would let you return the factor from the new table.

[Lauren1989]

Wow. I was making that so complicated. Thank you so much for an easy fix.

[pbaldy]

No problemo!