If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
New key fields?
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. |
#3
|
|||
|
|||
New key fields?
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], .......... |
#4
|
|||
|
|||
New key fields?
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 |
Thread Tools | |
Display Modes | |
|
|