related data needs 'moved' to different relationship

[mreed72]

I have a problem and can't get clear enough to figure it out.

2 tables in my database are related to each other.

table1 = contacts
table2 = Jobs

both are a 1to1 relation, using the standard ID fields to relate to.

now, each contact holds 1 specific job. Each contact has information associated with it. and it's related to the appropriate job (table2)

but 1 of the contacts has just decided he didn't like the job he was doing, and wants to do another job.

(the old way) I would delete him out of contacts, and create a new contact related to table2.

Is there a way to 'move' the relation of a contact from 1 job(table2) to another Job(table2) by way of a query?

[yarbrough40]

Sure... use an UPDATE statement.

UPDATE Jobs SET Job = 'Garbage Man' WHERE [Standard ID] = 'XXXXX'

[Delerna]

It would be easier to give you a good answer if you included the tables their fields/types itemised with an indication of which are the keys in the above description. Not every field, but the important ones.

Your definition of

standard ID fields

may be different to mine.