Newbie question: linking fields in Access (1 Viewer)

Hello, I am a newbie and would appreciate some novice information.

I have two tables, table 1 and table 2
Table 2 contains a long list of names each with an ID.
Table 1 contains just a few entries, each with an ID, which would correspond to the ID in table 2. I want to have a field in Table 1 that is the name associated with the ID in Table 2. How do I query Table 2 with the unique ID to have it pull the name into the name field of table 1?

I can provide any additional information.
Thanks for helping me to get started!
Josh

Does it look like this:

tbl 1
ID
Namefield1

Tbl 2
ID
Namefield2
etc

and the two ID fields correspond?

In that case, make an update query joined on the two ID fields and put [namefield1] in the "update to" row of namefield2.

Fuga.

There is no reason to store the name field in both tables. Simply base your form or report on a query that joins the two tables whenever you need the name field to be available.

Thank you very much for your help.

Pat:
The reason I want to update a field is because I am accessing the data from the table via an active server page (ODBC Data Source) , and I am not familiar on how to do this using a report or a form. I would not want to try to access the larger table via ODBC, because it contains so many entries.

Fuga:
I was able to create an update query with the directions you provided and was successful in linking everything together, as you described. Thank you so much for your directions with this! However, once I add additional data entries to the table, they do not update upon running the update query--only the entries that were present when I created the query continue to update. Do you have any ideas why this might be and how to solve it? Also, I was hoping these fields would update automatically when entries are added from the Active Server Page (ODBC Data Source)... without opening access and running the query program. Any ideas on this!

Thank you so much for your help with this. I am enjoying learning this really exciting program!

josh

If you created an update query with join on the ID fields, that means the query selects those entries that have the same ID in both tables. So if you add new ones to one of the tables, they will not be included.

You can solve this with an append query. (From what you describe, you could probably design the append query so that you will not have to run the update query any more. I don´t know exactly what your table structure looks like, though.)

If you want to have the table updated automatically, I think what you really want is a linked table. I´m not sure how to do it with the type of data you want to access, but try File -> get external data -> link tables, or the table wizard.

Also, consider Pat Hartman´s advice again. (It´s generally a good idea to do so )

Hope it works out.

Fuga.

Are you certain that you need to create the "smaller" table? This is a really poor solution in any application since you have duplicated data and are now having trouble coordinating updates. And in your case it is even worse since it sounds like updates are coming in both directions. Ie into the small table from the web and into the large table from your client server app.

When you retrieve records by key value, it makes very little difference how may rows a table contains. Direct access via an index is optimized very well and does not cause delays even in tables with millions of rows. We would need to have more details to help you optimize access but you should be able to use a query with criteria to limit the number of rows selected.

Pat,
Thank you very much for setting me on the right track.
Indeed, I figured out how to create queries, and they load beautifully onto my active server page, and combine the data just perfectly.
I really appreciate your direction here.
Josh