Interesting primary key problem

[Guest]

OK, I am stuck continuing someone else's work with our company's customer
service database. Basically we do some customer service functions on behalf
of other companies.

Currently, customers are indexed by a unique customer ID number, specified
by the client. Makes life easy for us, and the whole customer record set is
indexed by this number. Each client uses different number formats, so
duplication has never been an issue.

Problem - we have two new corporate clients. We must handle the calls for
these and generate for each one a customer number. To be compatible with the
current system, a ten digit number must be generated as "customer_id" but
obviously this must not duplicate those already in use.

I can't use an autonumber because I still have a requirement to be able to
enter the number manually in the case of our exisiting clients, but I need to
be able to guarantee non-duplication.

There is undoubtably a long way to do this, and if I think long enough i'll
work one out eventually, but I would really love a "quick and dirty" solution
if anyone has any ideas.

 

[Guest]

First off the primary key should be an autonumber. That will go a long way in
avoiding your current problem plus future problems.

Now that it appears that the CustomerID is not longer going to be unique,
you need to make the combination of the CustomerID and Client a unique index
to stop duplicates. Notice that I did NOT say the primary key. That should
still be an autonumber with no meaning nor reason to change. Link to other
tables using the autonumber in the FK field.

 

[John Vinson]

On Wed, 11 Jan 2006 08:49:02 -0800, "Alec M1BNK" <Alec

OK, I am stuck continuing someone else's work with our company's customer
service database. Basically we do some customer service functions on behalf
of other companies.

Currently, customers are indexed by a unique customer ID number, specified
by the client. Makes life easy for us, and the whole customer record set is
indexed by this number. Each client uses different number formats, so
duplication has never been an issue.

Problem - we have two new corporate clients. We must handle the calls for
these and generate for each one a customer number. To be compatible with the
current system, a ten digit number must be generated as "customer_id" but
obviously this must not duplicate those already in use.

I can't use an autonumber because I still have a requirement to be able to
enter the number manually in the case of our exisiting clients, but I need to
be able to guarantee non-duplication.

There is undoubtably a long way to do this, and if I think long enough i'll
work one out eventually, but I would really love a "quick and dirty" solution
if anyone has any ideas.

A couple of suggestions:

1. Consider using a two-field joint Primary Key, with the client ID
and their customer ID as the two fields. This would both let you track
the client in each record, and also allow different clients to use the
same customer ID.

2. Alternatively, create a table with ten thousand or so valid
ten-digit potential customer ID's. Use the Unmatched Query Wizard to
exclude those ID's already assigned, use DLookUp into this query to
set the Default Value property of the CustomerID field. This will give
you the next available ID and prevent duplicates.

John W. Vinson[MVP]

John W. Vinson[MVP]

 

[Guest]

Thanks guys, you've both given me good potential solutions, one of which is
bound to have minimal impact on the existing forms rules etc.

Alec