Update query based on 2 values in a field

[gschimek]

I'd like to build an update query, but it's a little complex and I can't wrap my head around it.

I have a table with, for this question, we'll say has 2 fields: ID and Experience. The Experience field has 1 of 6 possible values in it for each record. So the table may look like this.

ID - - Experience
1 - - Value1
1 - - Value2
1 - - Value3
2 - - Value 1
3 - - Value1
3 - - Value1
4 - - Value 6
3 - - Value1

What I'd like the query to select is any record ID that has at least 2 different of the 6 possible values in the Experience field. So in my example, ID 1 would be selected because that ID has 3 different values in the Experience field, but ID's 2-4 would not, since they all have only 1 unique value in the Experience field.

Selecting based on 1 value is easy, but I can't figure out how to select based on 2 or more unique values.

 

[jzwp22]

I would create a query that finds the unique combinations of ID and experience values (I called the table tblExperience for demonstration purposes)

query name: qryFindUnique
SELECT DISTINCT tblExperience.ID, tblExperience.Experience
FROM tblExperience;

I would then create a query that counts the number of records from the above query that have the same ID number. I would then nest that within another query that gets the ID you want. This is shown below:



SELECT ID
FROM (SELECT ID, Count(qryFindUnique.ID) AS CountOfID
FROM qryFindUnique
GROUP BY qryFindUnique.ID) as qryDistinct
WHERE qryDistinct.CountofID >1