Multiple Addresses, Need Only One per Person

[bigdavidt]

I am working on a query for a mailing to a list of health care providers. (There can be several entries in the source table for the same provider, since each provider can work at more than one location.) However, we do not want to send more than one piece of mail to each provider. How do I pull only one record for each provider? (It is not important which mailing address gets used, just as long as only one address is extracted for each provider.)

Thank you in advance.

 

[Remou]

Perhaps something like:

[tt]Select ID, SName, FName,
(Select Top 1 Address
From tblTable A
Where A.ID=tblTableID)
From tblTable[/tt]

Assuming that the names and addresses are both in tblTable.

 

[TheAceMan1]

. . . or perhaps this:

[tt]Note: [purple]PK[/purple] = PrimaryKeyName
[purple]PN[/purple] = ProviderFieldName
[purple]TN[/purple] = TableName[/tt]

[blue]SELECT [purple][b]PK[/b][/purple], [purple][b]PN[/b][/purple], OtherFieldName, OtherFieldName
FROM [purple][b]TN[/b][/purple]
WHERE ((([[purple][b]PK[/b][/purple]]) In (SELECT Max([[purple][b]PK[/b][/purple]]) FROM [[purple][b]TN[/b][/purple]] GROUP BY [[purple][b]PN[/b][/purple]])));[/blue]

See Ya! . . . . . .