Stupid Question: Update queries

[KhaymanUCSD]

I have two tables. The first is a list of program numbers and the program names they correspond to. The second has people's names and the program number they work under.

I want this update query to go into this table and read the program number, then find the corresponding program name in the other table and change the number to the name. Any help on how I could do this?

 

[shacket]

I am assuming you will be ridding yourself of the program table because you don't need it anymore? Anyway...

Create another field in your table for the program name. Update to the new field to the program name based on the program number field, then delete the number field that you no longer need.

Dave

 

[Rich]

What is your reason for doing this?

 

[KhaymanUCSD]

Well, I have a section in the database where we can edit lists for the values used in combo boxes in the data entry form. The data is stored in a number code format when they select the program from the combo box. What I'm trying to do is have a macro which first runs a make table query which pulls out their names and their programs. Then I want it to run a query which takes each of those records in the new table and changes the program code number to the name of the program they are involved in. Now I could write seperate update queries for EACH code number, but that would take a LONG time as there are over 40 different programs/codes.

Lists table has two fields: ID, Code
The new table has: First Name, Last Name, Program Code

I tried to find a way that I could just have the make-table query find the number and then match it to the name of the program when it made the table, but couldn't figure that out. So I tried to do it with an update query that could be run later in the macro. Expression builder was of little help.

I don't know if there's a better way to do this (I'm certain there is), but that's why I figured I'd post the question here.

 

[KhaymanUCSD]

Nevermind, I just realized how dumb I was being. I forgot that the fields were set up so that one was a number and one was text and that's why I was having so many problems. When I got the data types in sync and put together the relationships in the query, everything came out allright.