John, Thank you so much. Your query worked perfectly when I added one more
condition A.group='IG'. With this solution I don't have to use VBA code which
has recordset etc. I can directly insert this query into my automatic process.
I never used nested query in update statement before this gave me new ideas
as well. I have to master the nested queries more.
Thanks again.


John W. Vinson wrote:
Sample group TestNo
29045 IG Test 1
[quoted text clipped - 6 lines]
has same sampleid and different group( 'IG') as 'Test 2'. TestNo for record
should not change.

UPDATE tablename AS A
SET [TestNo] = "Test 2"
WHERE EXISTS
(SELECT B.[Sample] FROM tablename AS B
WHERE B.Sample = A.Sample
AND B.Group A.Group)

should work, if I understand you correctly (but back up your database first
because I probably don't!)

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201005/1