remap fields based on numeric key table

[brickbuilder]

Hi, I have a table with text fields that I want to remap to a new table with numeric fields based on a 'keys' table. Example original table:

ANIMAL	LOCATION
dog	barn
cat	garage

<tbody>
</tbody>

I want to use this keys table:

ANIMAL	Text	Key
	cat	1
	dog	2
	squirrel	3
LOCATION	house	1
	barn	2
	garage	3

<tbody>
</tbody>

To get to a new table that looks like:

ANIMAL	LOCATION
2	2
1	3

<tbody>
</tbody>

Any ideas please? Thank you!!
(if the keys table isn't layed out well what's a better way to lay it out?)

 

[Joe4]

Create two lookup tables, one for Animal and one for Location.
Then create a query in which you do Left Joins from your original data data to these two lookup tables, on the "name" (non-numeric) fields.
Then, just return the numerical values from the lookup tables in the query.

 

[brickbuilder]

Hey thanks for the response I think I can do that.
I have about 50 fields to do this with: Is your method still the most sensible way?
Thanks again

 

[Joe4]

Do you mean 50 categories, like ANIMAL, LOCATION, etc or do you mean 50 possibilities under each one?
If the the later, then yes, it does make sense to do it the way I suggested, since it only involves creating 2 lookup tables (it doesn't matter how many records are in each table).
I just want to make sure it is not a situation with 50 lookup tables, that might not be feasible.