Append Queries

[Treason]

I have 2 tables, both tables having identical fields. [Stud_ID],[Job],[Misc]

Table1 has all my records

Any records In that exist in Table2 also have a record in Table1 joined by [Stud_ID].

For Example, Table1 might have [Stud_ID]=1234 but the corresponding [Job] field for that record would be blank. Table2 may have [Stud_ID]=1234 and the corresponding [Job] Field will have some value. I am looking to "Replace" records in Table1 with the all corresponding records from Table2. So the query will grab the value of the Job Field from Table2, and append it into the Table1 Job Field, making sure that only replaces the value that are join by [Stud_ID]. My Append query never works correctly

This is my how my query design view looks...

Field: Job
Table: Table1
Update to: [Table2].[Job]

Can Someone write me SQL for this because I am really lost

 

[RV]

UPDATE Table1
INNER JOIN Table2 ON
Table1.Stud_ID = Table2.Stud_ID
SET Table1.Job = Table2.Job
;

Not to difficult I hope

RV

 

[Treason]

OK here's the story.. There really "shouldn't" be 2 different jobs for one Stud_ID. I tried the SQL that RV posted and I am getting the same wierd datasheet output I was gettin before. It shows the Job Column and 663 empty records. 633 happens to be the exact number of records in Table2. And I didn't mention before that the Stud_ID field in both tables are Primary Keys (No Duplicates).
I am thinking this is what Pat was getting at... Suppose there is one record in Table2 that does Not exist in Table1 or vice-versa. Could this be what it throwing my whole query off? And if it is, how can I single out that mismatched record, delete it, and make my append query function?

Thanks for the help guys

 

[Treason]

anyone?

 

[gblack]

See if this works
Open an Update query.
Choose The two tables you were talking about and link them by Stud_ID

Add the field from table that you wish to Update.

Then in the update to section enter [Table2].[Job].

Runit.