How to populate a field with a foreign key using 2 linked tables with SQL?

Question

I'm working in Access 2013 with Microsoft's SQL. Currently, I have three separate tables. One has a field [name] that is a number, table2 has a field with people's names [name] and a primary key for each name, and table3 is the same as table1 except that it's [name] field has the people's names from table2 as Short Text). I want to use [name] in table3 as a reference, so that I can put the corresponding ID number from table2 into table1.name. Is this possible, and if so how should I go about this? Hoping to write an SQL query.

table1
id | name (number)| other |
---------------------------
1 |               |  ...  |
2 |               |  ...  |
3 |               |  ...  |




table2
id | name (number)|
-------------------
10 | person1
20 | person2
30 | person3




table3
id | name (short text)| other |
---------------------------
1 | person2           |  ...  |
2 | person3           |  ...  |
3 | person1           |  ...  |

Basically, my goal here is to populate table1.name with a corresponding table2.id when table3.name = table2.name. For instance table1 row 1 has person2, so the person2 id will appear in the corresponding table1 row (where table1.id = table2.id). Sorry if I've done a poor job trying to explain this in writing!

Also, would it be easier to do this with 2 tables instead? Possibly have just table3 and replace the table3.name with values from table2.id when table2.name = table3.name. Although it would be putting a number in a field with datatype Short Text, not sure if this may cause issues. Thank you for any help!

perhaps describing your situation with actual schema and dummy data would help illustrate the problem better than describing it all with words. if it's not easy to understand after reading it once, it's not clear. — Tanner
better, perhaps update your description now to reference the columns in the schema you added... for example, you have field_1 that is supposed to be id I assume? — Tanner
Apologies, hope that helps. Don't post much so I'm not familiar with the formatting, thanks for your advice — J. Cal
Was able to solve this issue, will comment solution for future viewers. — J. Cal

J. Cal J. Cal · Accepted Answer · 2017-06-09T14:04:35

Solved this issue.

Ended up using just two tables, table2 and table3. I updated table3.name with values from table2.id where table3.name = table2.name.

The numbers are stored as Short Text, tried to change field datatype to Number but received an error that I might lose some of the data (8 values out of ~1000), although leaving it as Short Text will probably be fine.

UPDATE table3, table2
SET table3.name = table2.id
WHERE table2.name = table3.name;

If you still want to use three tables, I found that this worked well. Allows you to change datatype to Number beforehand so you don't risk losing data.

UPDATE table1, table2, table3
SET table1.name = table2.id
WHERE table2.name = table3.name
AND table1.ID = table3.ID

How to populate a field with a foreign key using 2 linked tables with SQL?

1 Answers