select distinct fulladdress

[lmv]

I need to select distinct on the fulladdress and I can't remember how to do it.
I only want 1 of each distinct address in my list.

help PLEASE! ;}

SELECT tblArea.AreaName, [Name List].MapCoord, [street number] & " " &
[street name] AS fulladdress, tblCity.City, tblCity.State, [Name List].ZipCode
FROM (TerritoryNumbers LEFT JOIN tblArea ON TerritoryNumbers.Area =
tblArea.AreaID) RIGHT JOIN (tblCity INNER JOIN [Name List] ON tblCity.CityID
= [Name List].City) ON TerritoryNumbers.TID = [Name List].TID
WHERE ((([Name List].TID) Like [What TID?]) AND (([Name
List].Status)="Active" Or ([Name List].Status)="Do Not Call" Or ([Name
List].Status)="Pending"))
ORDER BY [Name List].MapCoord;

 

[lmv]

I'm sorry I forgot... IF there is a fulladdress with a zip I want it to take
that one not the one without a zip.

I know SELECT DISTINCT will give me BOTH with and without seeing it as
distinct but I need to eliminate the additional fulladdres without a zip line
if there are 2.


Thanks

 

[John Spencer]

How about using an aggregate (group by) query and returning Max for the
ZipCode and Grouping on the other columns?

SELECT tblArea.AreaName
, [Name List].MapCoord
, [street number] & " " & [street name] AS fulladdress
, tblCity.City
, tblCity.State
, Max([Name List].ZipCode) as Zip
FROM (TerritoryNumbers LEFT JOIN tblArea
ON TerritoryNumbers.Area = tblArea.AreaID)
RIGHT JOIN (tblCity INNER JOIN [Name List]
ON tblCity.CityID = [Name List].City)
ON TerritoryNumbers.TID = [Name List].TID
WHERE ((([Name List].TID) Like [What TID?])
AND (([Name List].Status)="Active"
Or ([Name List].Status)="Do Not Call"
Or ([Name List].Status)="Pending"))
GROUP BY tblArea.AreaName
, [Name List].MapCoord
, [street number] & " " & [street name] AS fulladdress
, tblCity.City
, tblCity.State
ORDER BY [Name List].MapCoord;

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County