New key fields?

Hello All

I have a table that already has an autonumber key field called [empID],
which must be retained. For reason I won't bore anyone with, I now need to
add another field [newID] the values for which which must be editable, but
such that the value of [newID] must be unique for each value of the text
field [clientname]. The problem is that [newID] will initially have to be
empty for each new record, as its value will only be know some time after
the record is added, this means that duplications of [newID] for a given
value of [clientname] will have to be allowed where [newID] is empty.

So:
[empID] = 1, [newID] = 1, [clientname] = "abc"
[empID] = 2, [newID] = 2, [clientname] = "abc"
[empID] = 3, [newID] = 3, [clientname] = "abc"
[empID] = 4, [newID] = 1, [clientname] = "def"
[empID] = 5, [newID] = 2, [clientname] = "def"
[empID] = 6, [newID] = 3, [clientname] = "def"
[empID] = 7, [newID] = null/empty, [clientname] = "abc"
[empID] = 8, [newID] = null/empty, [clientname] = "abc"
[empID] = 9, [newID] = null/empty [clientname] = "abc"
.... are all OK, but

[empID] = 1, [newID] = 1, [clientname] = "abc"
[empID] = 2, [newID] = 1, [clientname] = "abc"
must be prevented.

I can't see how to do this, and have looked in Help but not got very far!

Hope someone can help.
Many thanks
Leslie Isaacs

On May 22, 11:49*am, "PayeDoc" wrote:
Hello All

I have a table that already has an autonumber key field called [empID],
which must be retained. For reason I won't bore anyone with, I now need to
add another field [newID] the values for which which must be editable, but
such that the value of [newID] must be unique for each value of the text
field [clientname]. The problem is that [newID] will initially have to be
empty for each new record, as its value will only be know some time after
the record is added, this means that duplications of [newID] for a given
value of [clientname] will have to be allowed where [newID] is empty.

So:
[empID] = 1, [newID] = 1, [clientname] = "abc"
[empID] = 2, [newID] = 2, [clientname] = "abc"
[empID] = 3, [newID] = 3, [clientname] = "abc"
[empID] = 4, [newID] = 1, [clientname] = "def"
[empID] = 5, [newID] = 2, [clientname] = "def"
[empID] = 6, [newID] = 3, [clientname] = "def"
[empID] = 7, [newID] = null/empty, [clientname] = "abc"
[empID] = 8, [newID] = null/empty, [clientname] = "abc"
[empID] = 9, [newID] = null/empty [clientname] = "abc"
... are all OK, but

[empID] = 1, [newID] = 1, [clientname] = "abc"
[empID] = 2, [newID] = 1, [clientname] = "abc"
must be prevented.

I can't see how to do this, and have looked in Help but not got very far!

Hope someone can help.
Many thanks
Leslie Isaacs

Doesn't compute. Null Null. You could just add a unique index to
that column and be done with it. As long as you allow Nulls you
should be fine.

Hi there,

if you need such tricks, it generally is an indication that your database
design is wrong. You shouldn't have doubles in a table...
I don't know your database design, so can't be sure, but it sounds like you
need 1 more table for the names with a 1 to many relatiionship to the current
table.

greetings
NG

"PayeDoc" wrote:

Hello All

I have a table that already has an autonumber key field called [empID],
..........

In message , PayeDoc
writes
Hello All

I have a table that already has an autonumber key field called [empID],
which must be retained. For reason I won't bore anyone with, I now need to
add another field [newID] the values for which which must be editable, but
such that the value of [newID] must be unique for each value of the text
field [clientname]. The problem is that [newID] will initially have to be
empty for each new record, as its value will only be know some time after
the record is added, this means that duplications of [newID] for a given
value of [clientname] will have to be allowed where [newID] is empty.

As someone else has already pointed out, there appears to be something
wrong with your table structure. What you have here are two entities
that should almost certainly exist in at least two tables.

One of these has the [empID] as its key. The other has [clientname] as
its key and this should also appear as a foreign key in the table which
has [empID] as its key. The [newID] field that you are trying to create
appears to be a surrogate key in the client entity. I can't see any
reason why you would want that when [clientname] is a perfectly
acceptable key in its own right.




--
Bernard Peek