Conditional expr: Assigning an index value to a range of numbers in a query field.

[vas90]

Hi all, this doesn't seem too complex but I'm having trouble finding a similar situation online.
I have a query with a field called IT Actual Hours. I would like to turn these hours into a weighted index.
For example:
IT actual hours: Number of hours-> assigned index value.

• • 0-10 -> 1
  • 11-39 -> 2
  • 40-120 -> 3
  • 121+ -> 4

So I would have an expression in a new field in that query with the index values. If anybody could assist, that would be much appreciated. I'm sorry for the simplicity of this. Thank you in advance.

 

[jzwp22]

I would set up a table that holds the ranges and the corresponding index values

tblIndicies
-pkIndexID primary key, autonumber
-longLowRange
-longHiRange
-longIndex

For the high range value of the last index, I would put in a very high number (some number that can never be reached), so the records in the table would look like this:

pkIndexID|longLowRange|longHiRange|longIndex
1|0|10|1
2|11|39|2
3|40|120|3
4|121|9999999|4

Then in your query where you have the IT hours, you can use either a nested query or a DLookup() to get the appropriate index value

SELECT ITHours, (SELECT longIndex FROM tblIndicies WHERE ITHours BETWEEN longLowRange and longHiRange) as WtIndex
FROM...

 

[vas90]

Hi jzwp22, thanks for your reply. I was able to create a module with the code to accomplish this.
Function Frequency(A As Variant) As Integer
If A = Null Then
Frequency = 0
End If
If A < 11 And A > 0 Then
Frequency = 1
End If
If A > 10 And A < 40 Then
Frequency = 2
End If
If A > 39 And A < 121 Then
Frequency = 3
End If
If A > 121 Then
Frequency = 4
End If

End Function

This ended up working. Thanks!

 

[vbaInet]

Vas90, you asked a similar question in this post and it was answered.

http://www.access-programmers.co.uk/forums/showthread.php?t=197059

 

[vas90]

That is very odd, I did not post that question in the other thread! I'm puzzled.

 

[vbaInet]

It's also very odd that both your starting and ending sentences are exactly the same?

Starting:
"Hi all, this doesn't seem too complex but I'm having trouble finding a similar situation online."

Ending:
"So I would have an expression in a new field in that query with the index values. If anybody could assist, that would be much appreciated. I'm sorry for the simplicity of this. Thank you in advance. "

Is it also coincidence that the writing style is the same plus the font is also the same? Oh and let's not forget you both have 1 post each. Hmmm...

 

[vas90]

Haha, I'm completely perplexed. I'm assuming I asked someone on another forum, they posted here the same question, got the answer, then got back to me.