Change value for multiple columns?

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

Guest

Hi,
Could you help me how to write a query to change the value in field
AGEcontain value from 1 to 9.
If Age =1 then change for field age1=20
if Age =2 then change value for field Age2=30
..... so on
if Age=9 then change value for field Age9=90
Thanks you.
MN
 
What are you trying to accomplish and why?

Your pattern makes no sense. on the second line why is Age2=30 instead of
20?

And what is Age2? Is that another field? How many fields do you have?
What do they mean?
 
Thank for consideration,
OK. I have a table with 11 fields. This table link to the another table with
client ID, this table keep track the age of client, for example:
If TableA.Age=1, then convert the value to TableA.Age1=20,
If TableA.Age=2, then convert the value to TableA.Age2=30,
If TableA.Age=3, then convert the value to TableA.Age3=40,
.....
If TableA.Age=8, then convert the value to TableA.Age8=90,
If TableA.Age=9, then convert the value to TableA.Age9=999
Client with field Age9=999 meaning unknow age
I can do with single update query like:
Update Dbo_Client Set dbo_client.age1=20
where dbo_client.age=1
Regards,
MN
 
In the form for client fill out. For example the form look like:
What is your age?
- Less than 20: o
- Less than 30: o
.....
o: Buble let the client mark into it

When we scan the form and import to database it fill in value 1 for age.
I saw somewhere comnad Split but in Access2K I don't see it?
Regards,
MN
 
Okay. Gotcha. Sounds like you want to take the selection number (1 -9) and
then place a value in a new field that would equal the actual age range. I
think you want to use the CASE statement in an update query to take the
calue in AGE and put a new value in another field. You imply that you have
many other fields, and I think proper database design would indicate you
should just have one. Maybe call it AgeRange.

I think you could use the CASE statement, or you could run it once where all
records with "1" in the AGE field would have the AgeRange field updated to
equal 20. Then run it a second time looking for all records with a '2' and
put in the value of 30, etc.

Hope that helps.

Rick B
 
Thank for reply,
But, I do not know how to apply CASE stat. in the query :-(
BTW, this table have 11 fields. And all of them will be use for different
kind of research.
Any advice?
Regards.
MN
 
Update Dbo_Client
Set dbo_client.age1= IIF(Age=1,20,Null),
dbo_client.age2= IIF(Age=2,30,Null),
dbo_client.age3= IIF(Age=3,40,Null),
....

I must say, I wonder why you bother to record the number 20,30, or 40 in the
different fields. It could be a requirement of the data you are populating, but
why not just record 'X' or '1' or some other value. The field is either checked
or it is not checked.
 
Thanks for reply,
Thank you for your advice, but the value is requirement for reseach purpose
so I do follow the user want it.
Again thank you & your query work very well.
Best Regards,
MN
 
Back
Top