New key fields?

  • Thread starter Thread starter PayeDoc
  • Start date Start date
P

PayeDoc

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
 
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
Click to expand...

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.
 
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
Click to expand...

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 said:
Hello All

I have a table that already has an autonumber key field called [empID],
Click to expand...
..........
 
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 said:
Hello All

I have a table that already has an autonumber key field called [empID],
Click to expand...
..........
 
PayeDoc said:
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.
Click to expand...

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.
 
PayeDoc said:
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.
Click to expand...

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.
 
Back
Top