Change Foreign Key Value in Access Query

Question

Here is a small example of what I would like to accomplish.

Table 1: Person

• ID
• FirstName
• HairColor_ID

Table 2: HairColor

• ID
• Color

I have Query 1 where I give it a color parameter(c) and it returns the ID of that Hair Color

SELECT HairColor.ID
FROM HairColor
WHERE (([HairColor].[Color]=[c]));

Now in Query 2, I have the Person table and the Query 1. In the field, I want to choose ID 2 of person and change its HairColor_ID according to the Query 1 result(which should return an ID), Query 2 is below:

UPDATE Person INNER JOIN Query1 ON Person.ID = Query1.ID SET 
Person.HairColor_ID = [Query1]![ID]
WHERE (((Person.ID)=2));

I would assume [Query1]![ID] returns the ID.

Basically all I want to do is update the HairColor_ID by giving the Color Value as a parameter. How would I do this?

Answer 1

Essentially what you want is to update a CROSS JOIN between Person and HairColor. You don't need Query1, and to me at least the mechanism is clearer without it.

PARAMETERS c Text ( 255 );
UPDATE Person, HairColor
SET Person.HairColor_ID = [HairColor].[ID]
WHERE Person.id=2 AND HairColor.Color=[c];