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
|
|||
|
|||
generate primary key by expression
I want to generate a primary key within a table by joining two fields
together. as an example block=MP30 paddock=1 to generate paddock_id=MP301 which will be the primary key. is it possible and how do you do it? -- peter |
#2
|
|||
|
|||
generate primary key by expression
It sounds like you'd like to have three fields, your two, plus a "primary
key" field that contains the two values added together (concatenated). DON'T! If each of the two fields you have contain meaningful data, don't waste a field to concatenate them. Concatenating is easy in a query. If you are saying the the combination of the two fields is/should be unique, you can used multiple fields together as a primary key, without any concatenating/combining. Regards Jeff Boyce Microsoft Office/Access MVP "retep" wrote in message news I want to generate a primary key within a table by joining two fields together. as an example block=MP30 paddock=1 to generate paddock_id=MP301 which will be the primary key. is it possible and how do you do it? -- peter |
#3
|
|||
|
|||
generate primary key by expression
Best way is to open the table in design view and Control + Click on the
two fields (Block and Paddock) and once they are both selected click on the Primary Key button (or select primary key from the menu - I can't recall which menu item it is under and can't check from this computer) '================================================= === John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County '================================================= === retep wrote: I want to generate a primary key within a table by joining two fields together. as an example block=MP30 paddock=1 to generate paddock_id=MP301 which will be the primary key. is it possible and how do you do it? |
#4
|
|||
|
|||
generate primary key by expression
I think this is difficult to do in this case as the paddock field will be
numbers, that are not unique and each block will have the same paddock numbers, eg MP30 has paddocks 1, 2, 3, 4 etc similarly MP31 has paddocks 1, 2, 3, 4 etc so if paddock is a primary key I can not have duplicate numbers (I think). Also I had intended that the concatenated block&paddock field is the related linked field in all other tables in the database. I could simply make the block&paddock field an entered field but to reduce human error I was hoping to automate. thanks peter "Jeff Boyce" wrote: It sounds like you'd like to have three fields, your two, plus a "primary key" field that contains the two values added together (concatenated). DON'T! If each of the two fields you have contain meaningful data, don't waste a field to concatenate them. Concatenating is easy in a query. If you are saying the the combination of the two fields is/should be unique, you can used multiple fields together as a primary key, without any concatenating/combining. Regards Jeff Boyce Microsoft Office/Access MVP "retep" wrote in message news I want to generate a primary key within a table by joining two fields together. as an example block=MP30 paddock=1 to generate paddock_id=MP301 which will be the primary key. is it possible and how do you do it? -- peter |
#5
|
|||
|
|||
generate primary key by expression
I think this maybe difficult in this case as the paddock field will not
contain unique values, ie block MP30 has paddocks 1,2,3,4,etc and block MP31 has paddocks 1,2,3,etc and hence can not be a primary key (I think). In addition I had intended using the block&paddock field as a related field in all other table in the database. I could make the block&paddock field a user entered field but was trying to automate it to remove human error. thanks peter "Jeff Boyce" wrote: It sounds like you'd like to have three fields, your two, plus a "primary key" field that contains the two values added together (concatenated). DON'T! If each of the two fields you have contain meaningful data, don't waste a field to concatenate them. Concatenating is easy in a query. If you are saying the the combination of the two fields is/should be unique, you can used multiple fields together as a primary key, without any concatenating/combining. Regards Jeff Boyce Microsoft Office/Access MVP "retep" wrote in message news I want to generate a primary key within a table by joining two fields together. as an example block=MP30 paddock=1 to generate paddock_id=MP301 which will be the primary key. is it possible and how do you do it? -- peter |
#6
|
|||
|
|||
generate primary key by expression
On Thu, 8 Jan 2009 17:06:03 -0800, retep
wrote: I think this maybe difficult in this case as the paddock field will not contain unique values, ie block MP30 has paddocks 1,2,3,4,etc and block MP31 has paddocks 1,2,3,etc and hence can not be a primary key (I think). In addition I had intended using the block&paddock field as a related field in all other table in the database. I could make the block&paddock field a user entered field but was trying to automate it to remove human error. thanks peter Jeff was not suggesting that either block or paddock be the primary key. Your misconception may be the assumption that a Primary Key must consist of only one field. It can be one field, or two, or even ten fields! Open the Table in design view. Ctrl-click Block and Paddock so that they are both darkened (selected). Click the Key icon. Now you can have records with duplicate blocks - or with duplicate paddocks - but you will NOT be able to have the combination duplicated; and you also won't have redundant error-prone calculated fields. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|