Helen,

At 11/26/2003 08:01 PM, Helen Borrie wrote:

>I always break out customer and supplier addresses into a 1:many relation
>from the customer table. It's an optional relation (won't exist until it is
>actually entered) and order entry operators have to enter an address if
>it's not already there or needs changing. Addresses each have an Address
>ID and a FK to the Customer (or Supplier) table and they have an Address
>Type (Billing, Delivery, etc.) and a status.
>
>At order entry, the operator either picks an address, modifies an existing
>one or adds a new one. If it's an override address, it still goes into the
>address table with a status of "override".
>
>You can extract the addresses you currently have. Any that are
>near-duplications (like your example) will show up in use and can be
>deleted the first time they are encountered. That will ensure that you
>don't accidentally delete addresses you needed to keep.

Yes, that makes perfect sense, and I have built a couple of applications
using this technique.

However, in this particular case, there is no operator intervention. The
sales orders are bulk loaded into the database, and the goal is to match
addresses, without concern for the customer. This is a shipping
application, and payment for shipping is based on a single address,
regardless of the number of orders delivered to that address. It is in our
best interest to match as many orders to the corrected address as possible.

I was hoping someone had implemented something similar, and could recommend
use of an algorithm or third-party product. I've found a few possible
products that look like I can implement via a UDF, so I'll pursue that avenue.

Thanks for your time and response.

Bob M..