Coding Options instead of nesting IIFs

[seocavaz]

I have some criteria I am using to "score" certain characteristics (small sample below.

	Number of Legs
Legs < 4	4 ≤ Legs < 6	6 ≤ Legs < 8	Legs = 8	Legs > 8
K or KH	10	10	8	6	4
/ or /H	10	7	5	4	3
X or XH	6	5	4	3	2

I was doing the expression as a really long nesting IIF, started with this:

Expr1: IIf([numberlegs]<6 And [governing braces]="k" Or [governing braces]="KH" Or [governing braces]="/" Or [governing braces]="/H",10).

However Its not working well, and there are many more criteria I need to add. Is there a better way to approach this scenario? I thought to maybe create the table and do a look up, but I wasn't sure how to approach that as well.

Any help would be greatly appreciated!

[CJ_London]

within the context of as query, investigate the switch and choose functions - google them to find out if they work for you.

In principle you should not hard code values that might change or be added to or removed, so putting them in a table is a good idea.

Moving on to the other part of the equation you can use IN

[governing braces] IN ("k", "KH", "/", "/H")

so you might have

Expr1:Switch(Legs<4,switch([governing braces] IN ("k", "KH", "/", "/H"),10,[governing braces] IN ("X ", "XH"),6),legs<6,switch([governing braces] IN ("k", "KH"),10,([governing braces] IN ("/","/H"),7,)([governing braces] IN ("X", "XH"),5...etc

with regards a table you would need something like

tblBraceValues
bracetype Legs quantity
K.............4......10
K.............6......10
K.............8......8

etc

you would link this table in your query on bracetype

and you would need an additional criteria for legs of

(SELECT Min(legs) from tblBraceValues as T WHERE Legs>mytable.numberlegs and bracetype=mytable.[governing braces])

which will select the right record

then Expr1 is just tblBraceValues.quantity

[seocavaz]

Great, thank you!

The criteria never changes so it wouldn't be bad to hard code it, but I still like the table idea better, however the "legs" can get up to a quantity of 50 so I'm thinking my only option is the switch expression?

[June7]

Choose() can also often be used instead of IIf() - not sure it applies to your situation.

[CJ_London]

personally, I would go the table route - it is much simpler for anyone else to follow you - and you in a couple of years time, and easy to extend if your number of legs increases. or the ranges change. No idea what the value means but perhaps a different material would introduce a whole new range of materials

however the "legs" can get up to a quantity of 50

So? just a few more rows in your table

[seocavaz]

Ha, yeah that will be an all nighter table build. Thanks for all your help!

[CJ_London]

that will be an all nighter table build

why? - you have 50 legs and 6 options - so a maximum of 300 records - if you are 'breaking' at every 2 then only 150 records - and if it is OK to combine K or KH in any situation they can be put into 1 record then only 75 records. The last would have a slightly different design

bracetype1 braceetype2 Legs quantity
K..............KH..............4......10
K..............KH..............6......10
K..............KH..............8......8

and your subquery would be slightly different

(SELECT Min(legs) from tblBraceValues as T WHERE Legs>mytable.numberlegs and (bracetype1=mytable.[governing braces] OR bracetype2=mytable.[governing braces]))

And since per the above, legs=4 quantity is the same as legs=6, you don't actually need to legs=4 record anyway

All I can say is you must be a very slow typer

[seocavaz]

Yeah once I thought about it for a second it was simple. I actually built the table in 2 minutes and did up to 200 legs ha.

[seocavaz]

Ok, I cant seem to get it right. I created the table according to the first scenario [Tbl_bracescoring], linked it to the query. Then when I went to put the subquery on the quantity field, it doesn't pull any records.

(SELECT min(legs) from [Tbl_BraceScoring] as T WHERE legs>[Tbl_Characteristic_Data].[NumberLegs] and bracetype=[Tbl_Characteristic_Data].[Governing Braces])

[CJ_London]

In your query, the subquery is a criteria for legs in the Tbl_BraceScoring table which should be joined to the Tbl_Characteristic_Data table on Tbl_Characteristic_Data.[governing braces] = Tbl_BraceScoring.bracetype

Something like

SELECT *
FROM Tbl_Characteristic_Data INNER JOIN Tbl_BraceScoring ON Tbl_Characteristic_Data.[governing braces] = Tbl_BraceScoring.bracetype
WHERE Tbl_BraceScoring.legs=(SELECT min(legs) from [Tbl_BraceScoring] as T WHERE legs>[Tbl_Characteristic_Data].[NumberLegs] and bracetype=[Tbl_Characteristic_Data].[Governing Braces])

by the way, suggest be consistent with regards naming conventions - better to avoid spaces (and other non alphanumeric characters)

[June7]

With exception of the underscore character.

[seocavaz]

Awesome, thanks! Also thanks for the tip!