I'm sorry if this is a bit off topic. It's more of a general database
question, not specific to IB/FB, but there are a lot of great DB people
here, so hopefully someone knows what I should do here.

I have a database with customer records, as well as other records, such
as sales, etc. that link to the customer record in a
1(customer):many(sales) relationship.

Now, there are several locations owned by the same business. They do
not have live connections to each others' databases. The different
locations' databases are sent to each other nightly, and kept separate.
We don't want each location's customer record populated to every other
location. We don't want the same customer in more than one location's
database unless they've actually done business at both locations.

If a customer comes to Location A, and is not found in that location's
database, the application will search each of the other locations'
databases to see if the customer is found there (searching by phone #,
name, etc). If that customer is found in, say, Location D's database,
it will be copied (without the other detail records) to Location A's
database.

The customer table has a key field (customerID) which is generated
automatically (sequentially) when the customer is added.

Goal: If a customer comes into a location, we want to be able to see
their sales history for all locations. We will do this by querying each
of the other locations' databases for that same customer, and showing
all of the sales records in one report, etc.

Problem: How do we find that customer in the other locations' databases?

I don't see a way of making it work with the key field (customerID),
because that field will be different for the same customer across
databases. With the different locations operating simultaneously,
there's no way to guarantee uniqueness of the customerID across
locations. When a customer is pulled from another location's database,
it can't necessarily just keep the same ID, because it's likely already
used for another customer at that location.

My first thought was to use another field (or combination of fields)
that is likely (even if not 100% guaranteed) to be unique (such as
name+address+telephone#). The problem is that if any of those fields is
changed at one of the locations, it breaks the link. Even if they do
eventually get the address changed at both locations, there's no
guarantee that the employee will enter it exactly the same.

Does anyone know how to solve this problem?

Thanks,
Joe