Query help! With unmatch records

[fluffyvampirekitten]

I have trying to tackle this scenario for a few weeks already .

I'm trying to find all the unmatch names in my Access Table.
However , I came accross this kind of scenario

Example :
In my Access table :
EXT_PAT_ID | NAME
1 | Teddy Bear
1 | Ted
2 | Teddy Bear
2 | Teddy Bear

With the code below

[DuplicatesPRODeposUPDATEsap.PAT_NAME) In (SELECT [PAT_NAME] FROM [DuplicatesPRODeposUPDATEsap] As Tmp GROUP BY [PAT_NAME] HAVING Count(PAT_NAME)< 2

My current result:
EXT_PAT_ID | NAME
1 | Ted


What I wanted to achieve is..
To check the ID( if id = id ) , then check if the names are not the same ( name IS NOT EQUAL name)
Then this will be the result. eg.
EXT_PAT_ID | NAME
1 | Teddy Bear
1 | Ted





Is there other way to achieve this ?
Or what's wrong with my query.
Please Help.
Thanks





Below here is my current code

SELECT DuplicatesPRODeposUPDATEsap.ID, DuplicatesPRODeposUPDATEsap.EXT_PAT_ID, DuplicatesPRODeposUPDATEsap.PAT_NAME, DuplicatesPRODeposUPDATEsap.GENDER, DuplicatesPRODeposUPDATEsap.DOB, DuplicatesPRODeposUPDATEsap.CREATE_DATE, DuplicatesPRODeposUPDATEsap.LAST_UPDATE_DATE, DuplicatesPRODeposUPDATEsap.C_SOURCE, DuplicatesPRODeposUPDATEsap.CAT_TYPE
FROM DuplicatesPRODeposUPDATEsap
WHERE (((DuplicatesPRODeposUPDATEsap.PAT_NAME) In (SELECT [PAT_NAME] FROM [DuplicatesPRODeposUPDATEsap] As Tmp GROUP BY [PAT_NAME] HAVING Count(PAT_NAME)< 2 )))
ORDER BY DuplicatesPRODeposUPDATEsap.EXT_PAT_ID;

[NTC]

Its not clear what you are doing. In general the No Match is a compare between 2 separate tables. Your post seems to indicate a compare within 1 table involving multiple records/fields. If my interpretation is correct, and I'm not sure that it is - then you need to use a union query to temporarily create a single field i.e.
1 [Teddy Bear Ted]
2[ Teddy Bear Teddy Bear]

and then set up a No Match on the new field you created. a 2 step process.

[JamesDeckert]

Can you do this
HAVING Count(ID & PAT_NAME)< 2

[fluffyvampirekitten]

Can you do this
HAVING Count(ID & PAT_NAME)< 2

It works ! Thanks a lot . I don't know that I can do this .

[JamesDeckert]

It works ! Thanks a lot . I don't know that I can do this .

I've never done it before, but it seemed reasonable. I tried it here and didn't get an error.