selecting distinct addres

  • Thread starter Thread starter Mohan
  • Start date Start date
M

Mohan

Hi

I have a table with client info. There can be more than one record with the
same address (different clinets living in the same address)
In the "Contacts' table, I have the follwoing filelds (amonth many other)
- ContactID
- FirstName
- LastName
- StreetNo
- StreetName
- City
- Prov
- PostalCode
- Country
- HomePhone
- CellPhone

All together there are about 400 records (but only about 360 houses)
How can I select the distinct addresses?

I tried combining the StreetNo & StreetName with address: [StreetNo] & " " &
[StreetName] and then selecting distinctrow but its not working.

Thanks
 
Create a query that selects the required fields, then switch from Design to
SQL view and insert DISTINCT after the word SELECT.

It will look somethin like this:

SELECT DISTINCT StreetNo, StreetName from AddressTable

Switch back to Design view. You cannot tell from Design view that it is
DISTINCT, but it will bring up just one record for each StreetNo/StreetName
combination.

Alternatively, you can GROUP the query by both fields (but still omit any
other fields from the query). That will have the same effect. You could also
use [StreetNo] & " " & [StreetName] as the only field in your query, but
group by it or SELECT DISTINCT.
 
Back
Top