Update FK field based on matching text in two other fields

[JoeBruce]

The Scenario:
My database contains 6 tables; the one that needs updating is [tblProgramsGiven]. In this table is a memo field called [Notes] and in many records I put a note in about the lead person who gave the program. I decided to make a new table - [tblRangers] - that includes all the people who give programs. It has an auto-number PK - [RangerID_PK] - and two text fields: [RangerFirstName] and [RangerLastName] After adding this new table, I added a number field to [tblProgramsGiven] which is [RangerID_FK] and joined the two tables (1-many).
The Goal:
I want to update [tblProgramsGiven].[RangerID_FK]; if the name found in [tblProgramsGiven].[Notes] matches [tblRangers].[RangerLastName] then it should update [tblProgramsGiven].[RangerID_FK] to match [tblRangers].[RangerID_PK]
The SQL:
Below is my amateur attempt to write the SQL for this update query.

UPDATE tblProgramsGiven INNER JOIN tblRangers ON tblProgramsGiven.RangerID_FK = tblRangers.RangerID_PK
SET tblProgramsGiven.RangerID_FK = tblRangers.RangerID_PK
WHERE tblRangers.RangerLastName = "*" & [tblProgramsGiven].[Notes] & "*";

What am I missing to accomplish my goal?

 

[The_Doc_Man]

Try this:

Instead of

tblRangers.RangerLastName = "*" & [tblProgramsGiven].[Notes] & "*";

use

tblRangers.RangerLastName [B]LIKE[/B] "*" & [tblProgramsGiven].[Notes] & "*";

 

[jdraw]

Joe,

I think it would be helpful if you showed us a jpg of your tables and relationships.

Could you have 2 or more rangers with the same lastName?

Good luck with your project.

 

[Ranman256]

Not good idea. Use 2 FK based on 2 fields of other table.

 

[The_Doc_Man]

UPDATE tblProgramsGiven INNER JOIN tblRangers ON tblProgramsGiven.RangerID_FK = tblRangers.RangerID_PK
SET tblProgramsGiven.RangerID_FK = tblRangers.RangerID_PK
WHERE ...

The more I think about this, the more confused I get. In English, this SEEMS to say:

When a name substring from the "Programs Given" table matches the last name of a Ranger in the "Rangers" table, cross link the program table to the ranger table; i.e. so that the programs table back-links to the rangers table.

It is a CROSS-link because the JOIN says that the link already exists in the other direction. (If it didn't nothing would be present in the working recordset to be updated.)

Which one is the parent and which one is the child? You already said it is a 1/many case. If ever you have two rangers assigned to the same program (and jdraw's question applies here, too) then only one cross-link exists to be updated; the last ranger record processed (that matches the WHERE criteria) is the one that gets cross-linked and any previous cross-links are overwritten.

In general, the Access SET THEORY model says that every change should appear to have occurred monolithically. However, in this case you have stumbled across a case where the monolith is a bit splintered. The update that "wins" is the update that is performed last. But since you never really know the internal order of records in an Access table, you never know which record really IS last.

You should know that an INNER JOIN for a one-to-many relationship potentially returns more records than are in the one-side table. If relational integrity is in force, that INNER JOIN returns as many records as are in the many-side table. So when changing something on the "one" side, remember that the "many" side has many things to say and only one place to say it.

Finally, it may be that I have misinterpreted your code and question - but that is what it looks like to me.