Query Help

[Zonie32]

Hi. I have a table with AgentID and AgentName fields. Each AgentID is unique. Depending on who does the data entry, sometimes they put different names in for the AgentName, ie...
AGENTID AGENTNAME
0000200 PURVIS INSURANCE AGENCY INC
0000200 PURVIS, PHILLIP L

I want to make a query where it finds those Agents that have the same number but their name is different. Does this make sense? Can someone help with a query? I usually use the design grid to make queries as I don't know SQL well.

 

[mpastore]

select agentid,agentname from mytable
group by agentid,agentname
having count(*) > 1

Mike Pastore

Hats off to (Roy) Harper

 

[PHV]

Something like this ?
SELECT A.AgentID, A.AgentName
FROM yourTable INNER JOIN
(SELECT AgentID, Count(*) As CountOfID FROM yourTable
GROUP BY AgentID HAVING Count(*) > 1) B ON A.AgentID = B.AgentID
ORDER BY 1, 2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[mpastore]

nix my example, it does not work, should have tested before posting.

Mike Pastore

Hats off to (Roy) Harper

 

[Zonie32]

PHV,

I tried your sql and it tells me "Syntax error in join operation.

 

[Zonie32]

Nevermind. I saw my error and it works great. Thank you PHV. You're awesome!