CODEWITHSUNDEEP
ms-accessms-access-2010ms-access-2013ms-access-2016
Derek Welton
Derek Welton

Reputation: 115

Change Foreign Key Value in Access Query

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

Table 1: Person

Table 2: HairColor

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?

Upvotes: 1

Views: 267

Answers (2)

HansUp
HansUp

Reputation: 97101

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];

Upvotes: 1

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

You are trying to join the Person table with Query1 via Person.ID = Query1.ID but Query1.ID isn't the ID of a person but of a haircolor...

This query should change the HairColor of person 2 to the color you enter as the parameter c:

UPDATE Person, Query1 SET Person.HairColor_ID = [Query1].[ID]
WHERE (((Person.ID)=2));

Upvotes: 1

Related Questions