Updating column based on varying criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 3 columns (taxable_ac, zone, levy) in a table named "temp".
I would like to update levy with taxable_ac*10 where zone=1 and
taxable_ac*20 where zone=2. I would prefer to avoid using multiple imbedded
if-statements if at all possible. Here is where I got stuck.

UPDATE temp SET temp.levy = taxable_ac*"10"
WHERE temp.zone="1";

I tried the following, but it obviously doesn't work. It should give you an
idea of what i'm trying to do though.

UPDATE temp SET temp.levy = taxable_ac*"10"
WHERE temp.zone="1" AND
UPDATE temp SET temp.levy = taxable_ac*"20"
WHERE temp.zone="2";

I just started using Access yesterday and am trying to get the hang of it.
Thanks for your help.
 
Rob,

You will need to write a function and write the query as below

update temp set temp.levy = GetTaxable_ac(taxable_ac,temp.zone)

In the VBA editor you will need to add a module like below:

Public Function GetTaxable_ac(ByVal Taxable_ac As Double, ByVal Zone As
String) As Double

Select Case Zone
Case "1"
GetTaxable_ac = Taxable_ac * 10
Case "2"
GetTaxable_ac = Taxable_ac * 20
Case Else
GetTaxable_ac = 0
End Select
End Function

I hope this helps.

Wade
 
If your are multiplying by the Zone value then the following might work

UPDATE Temp
SET Levy = [Temp].[Taxable_AC] * 10 * [Temp].[Zone]
WHERE Zone is Not Null

If Zone value is not a one to one correspondence with the multiplier, then
I would suggest adding a table that has the zone and the multiplier for that
zone and using that in the update query.

UPDATE Temp INNER JOIN tableZoneValue
ON Temp.Zone = tableZoneValue.Zone
SET Temp.Levy = [Temp].[Taxable_AC] * [tableZoneValue].[Multiplier]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I am trying to do something similar. I have a spreadsheet that I import, but
I would like to change the data in one of the fields and update it to
another. I've modified the function you created, but it isn't working.
What it is supposed to do is check the string in the field "OldReligion", and
based on the word in the Case statment, update the field "Religion" to
something else. Here's my code:

Public Function GetReligion(ByVal OldReligion As String) As String

Select Case OldReligion
Case "*catholic*"
GetReligion = "Roman Catholic"
Case "*baptist*"
GetReligion = "Baptist"
Case "*preference*"
GetReligion = "No Preference"
Case "*lutheran*"
GetReligion = "Lutheran"
Case Else
GetReligion = "0"
End Select
End Function
 
What are you getting for a result (or error)? Seeing your code would be
helpful in determining the problem. Thanks.

Wade
 
Instead of using a function I would build a translation table with two
columns PreferredValue and CurrentValue and then use that table in an update
query to fix all the values in the imported table.

But if you use the function - I believe you cannot use Select Case with
wildcards.

Public Function GetReligion(ByVal OldReligion As String) As String

IF OldReligion Like "*catholic*" Then
GetReligion = "Roman Catholic"
Elseif OldReligion Like "*baptist*" Then
GetReligion = "Baptist"
Elseif OldReligion Like "*preference*" Then
GetReligion = "No Preference"
Elseif OldReligion Like "*lutheran*" Then
GetReligion = "Lutheran"
Else
GetReligion = "0"
End if
End Function

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
John,

First, you definitely can't use wildcards in a select statement. As for
using a join to set the value in the update statment, it is a matter of
taste. I like the functions to allow me to use more complex calculations,
but for what the original poster wanted, your solution is much better.

Wade
 
I get a zero in the field as a result. But since John said that wildcards
don't work, then I need to find some other way to do what I want. John
mentioned translation tables. I don't know if that will work for me or not,
but I am going to look into it.
 
Well there are other ways to do this. You can use string manipulation in a
function or a like statment in SQL ( OldReligion like '%catholic%' ). It
really depends on how you are doing things.
 
I figured out the Like statement in an update query, but unfortunately the
field that contains the results can be one of 15 different results, and I
can't figure out how to write the sql statement. I had already posted my
issue before, but then this post came up so I was trying to see if I could
make work in my situation. My original post has a more detailed explanation
as to what I'm trying to do.
http://www.microsoft.com/office/com...91e549-aca4-432e-afc0-a756b83c8265&sloc=en-us
 
Well there are two things you can do. one is to create the select statement
in the function to be based on all of the 15 possible results as below:

select case OldReligion

case "Catholic","catholic", "Roman Catholic"

case "Anglican","Episcipalian"

so on and so forth

Now it will return the result you want. This is a similar idea to the one
offered dealing with a table. The other is you will need to nest iif
statements in the query, which is tedious, but it will allow you to use the
like sql statement.

iiif(OldReligion like '%catholic%,'Roman Catholic',iif(OldReligion like
'%baptist%','Baptist', etc...

Wade
 
I tried the IIF statement in the query:

UPDATE Contacts SET Contacts.Religion = IIf("OldReligion" Like
'*catholic*','Roman Catholic',IIf(IIf("OldReligion" Like
'*baptist*','Baptist'),IIf("OldReligion" Like '*lutheran*','Lutheran')));

but it didn't work. I ended up with an empty field. Now, contacts.religion
is a combo box. Could that be messing up my results?
 
First, I don't think you want to use double quotes around OldReligion. The
issue with the combobox depends, but right now I don't think it is the cause
of the issue.

Wade
 
I tried using single quotes and I also tried using contacts.oldreligion, but
neither worked as well. Right now I'm trying to create a vba module that
will do it. I don't see anyway of being able to do it within a single query.
 
Back
Top