How can I accomplish this with a query? - ID look up

[jjh]

I have a table (TBL_B) that receives record information from a program that I wrote. One of the columns in TBL_B that gets input data is system_name. By design, the value of system_name is already in another table in the database (e.g. the system_table).

At some point, I export TBL_B to an XML file that is consumed by an external program. As it turns out, I need to export the ID number (e.g. the key) of the entries in the system_name column, as well as text string of the values. I created another column in TBL_B called system_name_key_value, that is adjacent to the system_name column. What I don't know how to do is to structure a query that will search the system_table table for the value in the system_name column in TBL_B, and place the associated key in the system_name_key_value column. The values (e.g. names) in the system_name column are unique. Can anybody offer any specific suggestions, e.g. the actual query, as to how to do this? I would like to have the query run as soon as the value of system_name is entered into the table.

I know that doing this violates normalization rules of a db. Another approach I considered is linking system_table to TBL_B via 1:m relationship, but I can't figure out what the query should look like. Perhaps some help here???

Thanks for your help.

 

[Pat Hartman]

The ID field is what should be stored in TBL_B rather than the name field which is what you are currently storing. When you want the name field, use a query that joins TBL_B to the system_table.

You can use queries and tables interchangeably in Access so export the query that contains both fields rather than exporting the TBL_B table which (should) contains just the ID field.