generate primary key by expression

Discussion in 'Microsoft Access Getting Started' started by retep, Jan 8, 2009.

  1. retep

    retep Guest

    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
     
    retep, Jan 8, 2009
    #1
    1. Advertisements

  2. retep

    Jeff Boyce Guest

    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:D...
    >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
     
    Jeff Boyce, Jan 9, 2009
    #2
    1. Advertisements

  3. retep

    John Spencer Guest

    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?
     
    John Spencer, Jan 9, 2009
    #3
  4. retep

    retep Guest

    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:D...
    > >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

    >
    >
    >
     
    retep, Jan 9, 2009
    #4
  5. retep

    retep Guest

    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:D...
    > >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

    >
    >
    >
     
    retep, Jan 9, 2009
    #5
  6. 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]
     
    John W. Vinson, Jan 9, 2009
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Vuunderboy

    Primary Key Help Please

    Vuunderboy, Jun 29, 2003, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    372
    Allen Browne
    Jun 30, 2003
  2. Naresh Kumar Saini

    Help: Error - Index or primary key can not contain Null value.

    Naresh Kumar Saini, Jul 23, 2003, in forum: Microsoft Access Getting Started
    Replies:
    1
    Views:
    10,350
    Naresh Kumar Saini
    Jul 23, 2003
  3. Nick Mirro

    Doesn't 1:1 automatically generate record in foreign key table?

    Nick Mirro, Sep 8, 2003, in forum: Microsoft Access Getting Started
    Replies:
    5
    Views:
    334
    Van T. Dinh
    Sep 9, 2003
  4. cleaver

    Primary Key and Subform

    cleaver, Sep 25, 2003, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    140
    GVaught
    Sep 25, 2003
  5. g noseworthy

    won't set primary key-error 3022

    g noseworthy, Sep 29, 2003, in forum: Microsoft Access Getting Started
    Replies:
    2
    Views:
    186
    Guest
    Oct 3, 2003
Loading...

Share This Page