ACCESS Query

taverny
taverny used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Software Team Lead
Commented:
just use Group By? like:

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

Open in new window

taverny

Author

Commented:
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

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial