Assign value based on value field in query
in [Help]
|
From: Thomas Andersson on 13 Aug 2010 19:11 Salad wrote: > Thomas Andersson wrote: > >> I'm making a query where I want to create a new column with a value >> based on another columns content >> The source will contain either of 4 3-letter acronyms (RTB, MIA, >> RES, KIA). the new coumn should check this and assign a value of 1 >> for RTB and 0,5 for all others. >> How would I do this? >> >> > In the query builder enter the column name, a colon, then expression. > Ex: [A] is Acronym > > NumVal : IIF([A]="RTB",1,IIF([A]="MIA",0,5)) > > Whats the diff between 0 and 5 values? If they RTB (Return to Base) they get full credit for their mission, otherwise they only get half. (the second choise is 0,5 (half), not 0 and 5 BTW). T&hat solutions is the same I'm trying except you nested yours. Hmm, since I'm gonna use an additional query to count all the credits up maybe I should set it to 2 and 1 instead and then just half the total in the second query?
From: Stuart McCall on 13 Aug 2010 21:09
"Thomas Andersson" <thomas(a)tifozi.net> wrote in message news:8cm0okFu8hU1(a)mid.individual.net... > Thomas Andersson wrote: > >> I'm making a query where I want to create a new column with a value >> based on another columns content >> The source will contain either of 4 3-letter acronyms (RTB, MIA, RES, >> KIA). the new coumn should check this and assign a value of 1 for RTB >> and 0,5 for all others. >> How would I do this? > > I thought bthis would do it but keep getting errors > IIf([Sortie].[Result] = "RTB", "1", "0,5") Try using a period as a decimal point instead of a comma: IIf([Sortie].[Result] = "RTB", "1", "0.5") |