Re: Query Help with IF stmt

Thanks Hugo. It works great. Still little problem. I rushed the question and
clarify.
I am trying to pick employee randomly. So the rank field suppost to start be
like
EmpName EmpRank
a 1
b 2
c 3
d 1
e 2
f 3
g 1
h 2
i 3

For one system I will use all "1" and for other all "2" etc.
Thanks
Andy


"Hugo Kornelis" wrote:

> On Fri, 1 Apr 2005 07:23:11 -0800, Andy wrote:
>
> >Hi All,
> >Can someone please help me with below access query to convert to sql ?
> >UPDATE tblEmp
> >SET emprank = IIf('[tblemp]![ID] Mod 3', '[tblemp]![ID] Mod 3', 3).
> >Thanks in advance
> >
> >Andy
>
> Hi Andy,
>
> Hmmm, this seems to use undocumented (or maybe it is - but I couldn't
> find it) conversion of some numeric datatype to boolean in Access. I'll
> assume that 0 gets converted to False and 1 or 2 both to True.
>
> The equivalent in proper SQL would probably be:
>
> UPDATE Employees
> SET EmpRank = CASE WHEN ID % 3 = 0 THEN 3 ELSE ID % 3 END
>
> And some other ways to achieve the same result are:
>
> UPDATE Employees
> SET EmpRank = ((ID - 1) % 3) + 1
>
> UPDATE Employees
> SET EmpRank = COALESCE(NULLIF(ID % 3, 0), 3)
>
>
> However, if the EmpRank will always be based on ID as above, then the
> proper solution is to not store it at all - simply calculate the rank in
> a query, or (if you must) included it as a computed column. That way,
> you'll never have to worry about values getting out of sync anymore.
> (Of course, if this is just the starting value and it might change
> later, then this remark doesn't apply).
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.