Create new fields from one field codes

[Gonzo]

Working with queries, I need to create new fields in a table from one field
responses. I have one field, RACE, with possible answers 1 through 5. I
need to create one field per possible answer in the same record

Actual: record 1 - race = 2
record 2 - race = 3
record 3 - race = 5

I need:
record 1 - race_a = no race_b= yes race_c = no race_d = no race_e
= no
record 2 - race_a = no race_b= no race_c = yes race_d = no race_e
= no
record 3 - race_a = no race_b= no race_c = no race_d = no race_e
= yes

I am a standard user not a programmer. Any help can be highly appreciated.

 

[Klatuu]

Not good database normalization. The correct way is to have a PersonRace
table that has one record for each race for the person.

 

[Gonzo]

how can I get the values from the actual field RACE in the table PATIENT to
a new table PertsonRace with a record per each race.
Thanks.

 

[Klatuu]

I would suggest two tables.
First table would be RaceDescripton which would have two fields:
RaceID - primary key
RaceDescr

The second would be the PersonRace table, also with two fields.

PersonID - Foreign Key to person table
RaceID - Foreign Key to RaceDescription table

Build the RaceDescription table by hand. Use the RaceID to enter the value
of the race number and the RaceDescr to enter the description. It might look
something like this:

RaceId RaceDescr
1 Green
2 Klingon
3 Tralfamidorian
4 Romulan

Now, create an append query that creates a record in the PersonRace table
for each person.
If you already have multiple races for a person in one or more fields, this
will not work. If you do have multiple races in one field, describe how they
are structured and I can help you with that.

 

[Gonzo]

The table I m working is a maturetable and I can't make any change on it.
Following your input, how Can I create a new table, PersonRace, from my table
Patient representing one record for each race for that person?

 

[Klatuu]

I don't think you are understanding my point. You don't want multiple patient
records in the Patient table for a patient. It will still have only one.
The PersonRace table will have a record for each race for the patient. The
Patient table and the PatientRace table will be related by using the primary
key of the Patient table as a foreign key in the PatientRace table.