Multiple address, need to select one

[Noah114]

I have a query that pulls physician info. I then trim the doctors name due to the fact that the same doctor will be in the data multiple times with slight variances in info. For example;

Robert Hull 150 West Chester Dr,
Robert Hull 150 W. Chester Dr.

I trim and store the name in a separate table, for example simply

Robert Hull

How can I great a query that will grab on address, either or is fine, but when I run the data it dupes.

 

[randy700]

In you new table, set the "Name" field to accept no duplicates. By doing so, your query will fail when attempting to write the second "Robert Hull" record, but will continue looping through the rest of the table.


Randy

 

[Remou]

Try something like:
[tt]SELECT tblDoctors.DrName,
(Select Top 1 [Address] from tblAddresses
Where tblAddresses.ID=tblDoctors.ID) AS 1stAddress
FROM tblDoctors;[/tt]

 

[Noah114]

No such luck yet

 

[Golom]

SELECT D.DrName, MAX(A.Address) As [TheAddress]

FROM tblDoctors D LEFT JOIN tblAddresses A
     ON D.DrName = A.DrName

Group By D.DrName

Use a LEFT JOIN just in case there are Doctors who don't have an address defined.