Query to give me Companies with multiple addresses

A Table COMPANIES has two fields Company_Name and Address.
Some companies have more than one address.
I need a query to give me all the companies with more than one unique address please.
Fritz PaulAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
SELECT Company_Name, Address
FROM [Companies]
GROUP BY [Company_Name], Address
HAVING Count(*) > 1
0
mbizupCommented:
I don't think you'll get quite the right results grouping on both Company_Name and Address.

Try this:


SELECT Company_Name, Count(Address)
FROM Companies
GROUP BY Company_Name
HAVING Count(Address) > 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
... and if you need to display BOTH the company name and the address:

SELECT c.Company_Name, c.Address
FROM Companies c INNER JOIN
(SELECT Company_Name, Count(Address)
FROM Companies
GROUP BY Company_Name
HAVING Count(Address) > 1) q
ON q.Company_Name = c.Company_Name

Open in new window

0
Fritz PaulAuthor Commented:
Thanks that worked.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, I don't think mine or Miriam's will do precisely what you asked for, although her response might give you the result you were actually looking for.

1.  My first try would give you the Company/Addresss combinations where there are more than one of that combination of Company and address.
2.  Miriam's first try (the one you accepted) would simply identify all of the companies that have more than one address in the table, these may not be distinct addresses, which is what you asked for, but may not really be what you meant.

To get what you asked for: I need a query to give me all the companies with more than one unique address you need to first identify the unique combinations of [Company_Name] and Address.  Then, you need to identify the ones where there is more than one address for each company, so I would use:

SELECT Temp.[Company_Name]
FROM (
SELECT DISTINCT Company_Name, Address
FROM [Companies]
) as Temp
GROUP BY Temp.Company_Name
HAVING Count(Temp.[Address]) > 1
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.