Forum Discussion
Unable to run the queries in Ms access
Best practices of simplifying these type of complexity is to use Calculated Field in a table, specifically for simple calculations; also, it supports the IIF statement. That will eliminate creating a query for simple math results.
For following statment;
IIf([REFERENCE3]-0>30,[REFERENCE3]-0-30,0)+IIf([REFERENCE4]-0>30,[REFERENCE4]-0-30,0) AS mileDiff
Create a field, use the mileDiff as field name, simply copy paste the formula in the field definition. Later, you can use the field mileDiff in another calculated column;
,IIf(mileDiff>0,IIf([REFERENCE5]="DRIVE",16,IIf([REFERENCE5]="D2D"....
FYI, calculated field does not support Large text.
I would probably avoid calling calculated fields in tables "a best practice". Otherwise, this could work.
One of the dangers of terms like "Best" is that they tend to imply a consensus that simply doesn't, and can't exist in the real world. Absolutes are never good. 