Re: Query Help with IF stmt
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 01 Apr 2005 22:14:17 +0200
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)
.
- Follow-Ups:
- Re: Query Help with IF stmt
- From: Andy
- Re: Query Help with IF stmt
- References:
- Query Help with IF stmt
- From: Andy
- Query Help with IF stmt
- Prev by Date: Use Results From a Select as the "From" for Another Select
- Next by Date: Re: Use Results From a Select as the "From" for Another Select
- Previous by thread: Query Help with IF stmt
- Next by thread: Re: Query Help with IF stmt
- Index(es):
Relevant Pages
|
|
