Update 1 column from different table

[seb2]

I feel like I'm going insane and that this is should be simple...

I want to update Students.TestRetakes with data from ProblemStudents.CountofRetakes. Students has many more records in it than ProblemStudents. Table relations are connected by SS#.

I keep getting either only those records from Students that are also in ProblemStudents OR I get it added to the end of the Students, not corresponding with the appropriate records.

What should I do to just insert CountofRetakes into Students and keep Students whole?

 

[JeremyNYC]

Well, the real issue is that you have two tables, when there should probably only be one. Problem Students should be part of Students, with a flag to indicate that they need help.

You say you want to "insert" count of retakes. Do you mean update a field or add a new record?

It looks like you want to update a field.

If that's the case, you want to do an update query. It will be something like this...

UPDATE Students INNER JOIN ProblemStudents ON Students.SS# = ProblemStudents.SS# SET TestRetakes = [CountOfRetakes];

Back up your data before trying this.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.