Matching id values between two tables

[Nimo]

Hi, I have two tables, one has two fields:
student ID and student name

its kind of like this:

1 Mark
2 Tom
3 Franklin

the other table has three fields: student name, student classes, it goes like this:

Mark calculus
Mark Biology
Tom Statistics
Franklin Calculus
Tom Chemistry


what I want is for the second table to have its related id from the first table so it could be like this:

1 calculus


1 biology
2 statistics
3 calculus
2 chemistry

Is this possible ?


I cant simply make find and replace because the records are a lot is there another way should I relate the tables of something how will it work?

[JoeM]

This is simply a matter of creating a query using these two tables, joining on the Student Names fields in each table.
Then, just have the query return the ID field from the first table, and Class field from the second table.

[ranman256]

Make Upd qry and join the 2 tables on NAME, and update the [ID] fld.

[June7]

The StudentID field is Number type (Autonumber)? Add a number type field to Table2. Build query that joins the tables on the name fields. Assumes no names are duplicated for different students (there is only one student named Tom). Change the query to an UPDATE. Populate the new field with ID value from Table1. If all correct, delete the name field from Table2.

[Nimo]

I just tried this when I hit run I get nothing.. I hit the update option and typed [table1]![Id] and I hit run but nothing happened.. this is a screenshot:

http://i725.photobucket.com/albums/w...ps64851ddd.png


did I do it wrong?

[rpeare]

you may need to make the NAME field on the left hand table a primary key (temporarily) so that the recordset is updatable.

[Nimo]

I did what you said, the query runs but still returns an empty ID field it didn't update it didn't take the value from the 1st table

[JoeM]

Please switch your query to SQL View, and copy and paste your SQL code here.

[Nimo]

It turns out the run option didn't return any values because the enable content option was disabled from the access settings.. I luckily figured it out when I was trying to append records to a different database and a window popped up.. The run update returned the records it worked guys, thank you so much !