How to get UNIQUE CompanyID

[petrovlis]

I have now Access-qry (105.000 names) like:
CompanyID COMPANY NAME
1234 Johnson Inc Peter
1234 Johnson Inc John
4445 Walker Ltd William
5555 Stenton Inc Hank
5555 Stenton Inc Anna

How to get the qry that I see only the UNIQUE CompanyID
being 1234,4445,5555 so only ONCE that listed company.
Thanks for help
William

 

[Lothario]

SELECT DISTINCT CompanyID, COMPANY from yourtable

-L

 

[PHV]

And which name should be retrieved ?
A starting point:
SELECT A.CompanyID, A.COMPANY, First(A.NAME) As SomeName
FROM yourTable A
GROUP BY A.CompanyID, A.COMPANY


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

 

[petrovlis]

From William, correction:
I mean that I have Company table and Persons table linked
on CompanyID,
One company,(sometimes) many Persons
For making a new table I need to have only ONE(unique) companyname and one(first) Person of the linked person table.

Bye
William

 

[petrovlis]

More info from Petrovlis/William about UNIQUE

I have now
SELECT Companies.Company, Companies.CompanyID, Persons.CompanyID, Persons.LastName, Persons.Email, Persons.MainPerson
FROM Companies LEFT JOIN Persons ON Companies.CompanyID = Persons.CompanyID;

but this give for some IDs/Companies several same names
and I need UNIque IDs or Unique Companyname.

Thanks for any help