generate primary key by expression

[retep]

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?

 

[Jeff Boyce]

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

 

[John Spencer]

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]

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

 

[retep]

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

 

[John W. Vinson]

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.