Update 1 column to many column

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

Guest

Hi,
Could you help me out, thank in advance.
TableA, Fields: Race,White,Black,Asian,Other
I do have value in race is 1,2,3,9
If race=1 then White=1
if race=2 then Black=1
if race=3 then Asian=1
If Race=9 then Other=1
How can I write in Update query?
 
Sounds like you need to restructure your database (maybe that is what you
are doing). But, if race 1 is white, then you don't also need a field
called "white". Sounds like this is bad design.

Rick B
 
Thank for reply,
I want to remove column race out of DB but I need to convert the values from
field Race to White,Black,Asian,Othe.
I could update it by run a single query but I want to learn how to write a
query like this case.
Best Regards,
MN
 
One other note, you might want to make your new fields (white, asian, black,
etc.) into yes/no fields. A true value would then be -1 (not 1) and a false
value would be 0.

As far as how to do what you are trying to do, I would think that you would
wnat to create an update query and pull all records where race=1, then
update "white" to -1. Modify your query to pull all records where race=2,
then update "black" to -1.

Hope that helps,


Rick B
 
UPDATE your Table
SET White = IIF(Race=1,1,null),
Black = IIF(Race=2,1,null),
Asian = IIF(Race=3,1,Null),
Other = IIF(Race=9,1,Null)
WHERE Race is Not Null

Now that I've posted a solution, let me say that what you are doing is very
probably a bad thing. It will make your life tougher in the long run.
 
Thanks for all reply,
Thanks to the group very acknowlege the problems.
Best regards,
MN
 
Not necessarily bad, there is no implied racism in the information given.
The numerical equivalents may simply be indices.

Local Councils keep ethnic codes in respect of benefit claimants because the
data, coupled with geographical information, can show problems relating to
race, ghettoisation & crime, for example.

Terry.


:
: UPDATE your Table
: SET White = IIF(Race=1,1,null),
: Black = IIF(Race=2,1,null),
: Asian = IIF(Race=3,1,Null),
: Other = IIF(Race=9,1,Null)
: WHERE Race is Not Null
:
: Now that I've posted a solution, let me say that what you are doing is
very
: probably a bad thing. It will make your life tougher in the long run.
:
: MN wrote:
: >
: > Hi,
: > Could you help me out, thank in advance.
: > TableA, Fields: Race,White,Black,Asian,Other
: > I do have value in race is 1,2,3,9
: > If race=1 then White=1
: > if race=2 then Black=1
: > if race=3 then Asian=1
: > If Race=9 then Other=1
: > How can I write in Update query?
 
I believe what John was saying was a bad thing was the change in
database design.

At present there is one field which can contain one race code. By
deleting the race field and replacing it with four new fields you are
breaking normalisation. You now have four fields for one attribute
meaning that three of the fields on each record are pretty much
redundant.

Going this way could allow discrepancies to enter into database (e.g. a
record where White=True, Black=True, Asian=False, Other=False).

Any future changes to how race is recorded will need additional changes
to the database and application design. With one field related to
another table (races) you can easily decide to split out the races
(e.g. White European, White British, White Irish, White Other which are
used in Britain).

I have to wonder why you would really want 4 fields when one would do.
You could achieve the appearance of four fields in the GUI by use of an
option group based on the one field.

Barry-Jon
 
Back
Top