asked on

ACCESS Query

Hi Experts,

I need to create a query that will return me all the unique records based on few fields. I am gonna try to explain by example.
here is a table with few columns :
Table name : Contact
Fields name: Company, Address 1, address 2, City, State, district #
Company Adress1 address 2 city state distric #
abc 124 street 222 door Miami FL #03
abc 123 street 222 door Miami FL #04
abc 123 street 222 door Miami FL #02
abc 123 street 222 door Miami FL #03
abc 123 street 222 door Miami FL #04
abc 123 street 222 door Miami FL #03
abc 123 street 222 door Miami FL #03

the output will be
abc 124 street 222 door Miami FL #03
abc 123 street 222 door Miami FL #04
abc 123 street 222 door Miami FL #02
abc 123 street 222 door Miami FL #03

I would like a query that will show me all the unique company based on the fields : company , address 1 and district#.

I am trying to figure out how many unique company exists in a single district and have each district unique company on their own table.
Can someone help me ?

Thanks

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

taverny

ASKER

great that worked.
Thanks , I have another query to design. It's gonna be a little more complicated .

I am gonna open another question now.

Thanks

bcnagel

Hi taverny.

Ryan Chong's suggestion is a good one. Because you indicated that you want uniqueness only on company, address1 and district #, I'd run a query like this one:

Select Company, [Address 1], [district #]
from Contact
Group By Company, [Address 1], [district #]

Open in new window

If you still want to return the address 2, City, State fields, you could run a summary function over them, like

Select Company, [Address 1], [district #], max([address 2]) as [address 2], 
max(City) as City, max([State]) as [State]
from Contact
Group By Company, [Address 1], [district #]

Open in new window