Trying to select distinct records from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Trying to select distinct records from table

Author

Topic

lmnorms1
Starting Member

7 Posts

Posted - 2008-05-01 : 14:51:53

Hello,
I am using access to select distinct data from a table. The data may have multiple occurences so I need to get the based off a a key - license no.
Here is what I have so far:
SELECT TakeOutExtractMain.AGENCYNAME, TakeOutExtractMain.AGENCYTAXID, TakeOutExtractMain.AGENTLASTNAME, TakeOutExtractMain.AGENTFIRSTNAME, TakeOutExtractMain.AGENCYADDRESS1, TakeOutExtractMain.AGENCYADDRESS2, TakeOutExtractMain.AGENCYCITY, TakeOutExtractMain.AGENCYSTATE, TakeOutExtractMain.AGENCYZIP, TakeOutExtractMain.AGENTPHONE, TakeOutExtractMain.LICENSE
FROM TakeOutExtractMain
WHERE TakeOutExtractMain.LICENSE in (select distinct TakeOutExtractMain.LICENSE FROM TakeOutExtractMain)
GROUP BY AGENCYTAXID, TakeOutExtractMain.AGENCYZIP, TakeOutExtractMain.LICENSE, AGENCYNAME, AGENTLASTNAME, AGENTFIRSTNAME, AGENCYADDRESS1, TakeOutExtractMain.AGENCYADDRESS2, TakeOutExtractMain.AGENCYCITY, TakeOutExtractMain.AGENCYSTATE, TakeOutExtractMain.AGENTPHONE;

If I remove the where clause it works. But I get the duplicates.

Any advice would be appreciated.
Thx.
Lmnorms

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-02 : 07:11:05

the general way to work this is

select myfield, count(*) from mytable
group by myfield
HAVING count(*) > 1