Create a look-up table?

[Kurtosdf]

I need to convert the values fall, spring, summer to 1, 2, 3 in order to calulate on. Then I need to convert them back. Is a look-up table the best solution for this? How would I go about doing this?

Any advice is appreciated
Kurto

 

[caibo]

Use a function to return the numeric value for use in the calculation.

Function GetSeason(strSeason as String) as Integer
   Select Case UCase(Mid(strSeason,1,2))
       Case "FA"
          GetSeason = 1
       Case "SP"
          GetSeason = 2
       Case "SU"
          GetSeason = 3
   End Select
End Function

Peter Caine
caibo@wwdb.org

 

[JerryDennison]

You're best bet is NOT to do multiple updates. But create a small lookup table with a primary key using the numeric values and a descriptor field for what you'd like that value to represent.

 

[Kurtosdf]

I cannot get that to work. Can you give me a bit more instruction?

Thanks

 

[jgoodman00]

Create a lookup table, called tblLUPSEASON or something, & in that table create an autonumber field. Set this as the primary key, & create a second field called season. Set this as a text field & then enter the details. A number will automatically be assigned to each season. Next go into the setup of your main table & under data type select the lookup wizard. It should automatically create a combo box which will display the text, but store the numeric value corresponding to it.Then your calculation is simple, & the user only ever see's text

 

[Kurtosdf]

I did the lookup thing and got that ok. But when I run my query I am still not getting the correct results. It is showing duplicate records and not all of them that should be there based on the search criteria?

 

[ntp]

Why create a lookup table when a function can more than handle your four possiblevalues. Use caibo's solution. You can call the function in your query wherever you want the numerical value.

"Select GetSeason(table1.Season) as NumericalSeason FROM table1"

No need to use a lookup table. Seems to me like overkill.

ntp