PC Review


Reply
Thread Tools Rate Thread

generate primary key by expression

 
 
retep
Guest
Posts: n/a
 
      9th Jan 2009
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
 
Reply With Quote
 
 
 
 
Jeff Boyce
Guest
Posts: n/a
 
      9th Jan 2009
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" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
>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



 
Reply With Quote
 
 
 
 
John Spencer
Guest
Posts: n/a
 
      9th Jan 2009
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?

 
Reply With Quote
 
retep
Guest
Posts: n/a
 
      9th Jan 2009
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" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> >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

>
>
>

 
Reply With Quote
 
retep
Guest
Posts: n/a
 
      9th Jan 2009
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" <(E-Mail Removed)> wrote in message
> news(E-Mail Removed)...
> >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

>
>
>

 
Reply With Quote
 
John W. Vinson
Guest
Posts: n/a
 
      9th Jan 2009
On Thu, 8 Jan 2009 17:06:03 -0800, retep <(E-Mail Removed)>
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]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Do I have to set the primary key to include primary key fields? boooney Microsoft Access Database Table Design 3 15th Apr 2010 12:17 AM
Data Primary key vs. Artificial (Autonumber) primary key M. Microsoft Access Database Table Design 78 14th Dec 2007 10:10 AM
Re: How to build a funciton to return primary key,alternate key and foreign key fields Allen Browne Microsoft Access VBA Modules 0 18th Jul 2005 01:51 PM
How to build a funciton to return primary key,alternate key and foreign key fields tonyck Microsoft Access VBA Modules 0 18th Jul 2005 01:07 PM
Cannot add primary key constraint since primary key is already set for the table Microsoft VB .NET 1 3rd Dec 2003 02:14 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:49 PM.