1

Hello I am currently stuck on a problem. Any help will be appreciated. The data I am looking to classify are weights ranging from 0-80. If the line item is between 0-19 tag it as light 19-39 medium, etc... I am struggling for a starting point for how to get this to work. This query is pulling multiple fields from the data source but this is the only one that is being modified. After this the data will be re-classified based on light, medium, etc.

Thanks for the help. Let me know if anything is needed

pere27
  • 11
  • 3
  • Post some sample data, an example of required output plus any effort you've done so far yourself. – Rene Sep 09 '16 at 01:27
  • You can use nested iifs or a switch statement – Don George Sep 09 '16 at 12:31
  • Here is what I have I tried a switch but it didn't work Switch( SteelData.[Unit Weight] <19.0001,Light, SteelData.[Unit Weight] >19.0001 and < 39.00001, Medium, SteelData.[Unit Weight] >39.0001 and < 79.00001, Heavy, SteelData.[Unit Weight] >79.0001 and < 119.00001, Extra Heavy, SteelData.[Unit Weight] >119.0001 and < 394.00001, Extra Extra Heavy, SteelData.[Unit Weight] >394.0001, Extra Jumbo ) As Classification – pere27 Sep 09 '16 at 12:49
  • I want the output of the Query to look like this Classification Unit Weight Length Light 15 10 Medium 25 56 – pere27 Sep 09 '16 at 12:49

2 Answers2

3

You can make a table called WeightClasses

enter image description here

Then you can join to this

Select t.*, wc.weightclassname
From Things as t
inner join WeightClasses as wc 
    on t.Weight >= wc.LowerBound and t.Weight < wc.UpperBound

This will produce results like this

enter image description here

I think this is a lot easier to read and auditable than IIF statements. You can change your classifications in a table without changing code and you can make sense of your classes very quickly and easily by looking at the table. You can also add weight classes just by adding new records.

Brad
  • 11,934
  • 4
  • 45
  • 73
0

USE IIF like this:

IIF(SteelData.[Unit Weight]<19.0001,'Light',IIF(SteelData.[Unit Weight] < 39.00001,'Medium',IIF([etc.])))

SunKnight0
  • 3,331
  • 1
  • 10
  • 8