Populate a Table with IF function

[GriffyNJ]

I'm a currently working on an access database in which i've run a series of APPEND queries to populate a NEW TABLE Ive created. What I need to do is populate the RACE field in this new table with data based on 5 different RACE fields from another table entitled PERSON. So what I have is a table called PERSON which has fields entitled RACE BLACK RACE WHITE RACE ASIAN RACE HISPANIC RACE UNKNOWN which all have 1's where applicable for their records. I need to somehow tell ACCESS that if RACE BLACK in the PERSON table is equal to 1, then populate the corresponding record in the NEW TABLE with a 2 for black. I'm thinking it would be a simple if statement in one of my queries, can any one give me direction????

 

[datAdrenaline]

Switch([RACE BLACK]=1,1,[RACE WHITE]=1,2,[RACE ASIAN]=1,3,[RACE HISPANIC]=1,4,[RACE UNKNOWN]=1,5,1=1,0)

Switch() will return the corresponding value of the FIRST true expression ... so a 1,2,3,4,5 will will returned if one of the RACE* fields is marked with a 1, if NONE are marked, the 1=1 expression will be the FIRST True expression and a 0 will be returned by Switch() ...

 

[georgedwilkinson]

You said the "race" was in another table, why not just look it up in the table?

BTW, your table(s) is/are not normalized. You'll save yourself a lot of problems (like this one) by fixing it. You only need a single "race" column which holds the value. And since a person is born with and dies with a race, it would be appropriate to put that column in the "Person" table.

 

[datAdrenaline]

Hello George ...

I think the OP is moving FROM a de-normalized table to a normalized one ...

His comment ...

>> series of APPEND queries to populate a NEW TABLE Ive created. What I need to do is populate the RACE field in this new table with data based on 5 different RACE fields from another table <<

Is what leads me to beleive that.

 

[georgedwilkinson]

Hmmm...good catch. I re-read it and that's what it sounds like. My ADD strikes again!

 

[datAdrenaline]

.... ummm .... what were we talking about ... where am I going ... wait ... oh yes my keys!! ... so I needed to get hot dogs ... or ... was that dog food ... ARRGGH!!! ...

... As you can see ... THAT .. never happens to me! ...

 

[georgedwilkinson]

That's a pretty fair assessment. LOL!

 

[GriffyNJ]

That did the job.. Thanks a million guys I appreciate you helping me

 

[georgedwilkinson]

Glad Brent could help. Happy to be here for comic support.