Update/Change existing Data to new Table with Query?

[kioku]

i'm a Access novice. I have tables with existing data in numerical form, and would like to know how I can use Queries or VBA code to update these values into a new format in a new table. for example the original data might be of race type:

1 - caucasian
2 - african american
3 - hispanic 1
4 - hispanic 2
5 - hispanic 3
6 - other

and i want to regroup these into less types, eg:

1 - caucasian
2 - african american
3 - hispanic
4 - other

how can i achieve this in Access? i know how i can do this conceptually with "if" and "case" statements, but I have no idea how i can do this in Access. I don't want to mess with the original data, so please help with CODE or QUERY examples.

thanks!!

 

[MarkK]

Rather than create a new table, which would then require constant re-creation as data in the original table changes, you can simply write a query against your existing table to format the data as you wish. In the case you've described you can write a query like...

SELECT iif(raceID > 5, 4, iif(raceID > 2, 3, raceID)) as raceID FROM ...

more readably, this returns

select case raceID
  case > 5
    return 4
  case > 2
    return 3
  case else
    return raceID
end select

and it preserves the original data source.
If you still need to create a new table, use the same logic in a Make Table query.

 

[kioku]

thanks for your reply.

so if i want to do "=" i can just replace the ">" ?

and what if I'd like to do an OR with multiple values, how can i incorporate that in the SQL statement?

if I do want to use the VBA code, can i do that in the query?

thanks again, since i know little about SQL/VBA.

 

[MarkK]

Yes, you can use all comparison and logical operators in SQL. Yes you can call a function from a query. An example of the latter is...

"SELECT Mulitply(YourField, 2) as Product " & _
"FROM YourTable " & _
"WHERE ItemID = Multiply(3, 3)"

And the function, in a standard module, would be...

Public Function Multiply(factor1 as single, factor2 as single) as single
  Multiply = factor1 * factor2
End Function

 

[kioku]

thanks again. how can i use the "OR" function within SQL?

i can't seem to do: IIf([raceID]=2 OR 4 OR 5,6,[raceID])?