Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Primary Key question (Access XP)

    Hi,

    I have a database with 2 tables: Bldg and Work_Record Both of them have 2 common fields: Sector_No and Plot_No The combination of these 2 fields is unique for every Bldg record. But while creating a Bldg record, the fields are entered by the user as 2 separate fields. What is the best way to connect the 2 tables using a combination of these 2 fields.

    Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Primary Key question (Access XP)

    I don't know if this is what you are asking.

    When you create a relation between two tables by selecting Tools/Relations..., selecting the tables and then dragging a field from one table to the corresponding field in the other table, Access shows a dialog in which you can specify the details of the relation. By default, only one field on each side is included, but you can include more fields manually. See the attached picture (the captions are in Dutch, but you'll get the idea, I hope).

    If I misunderstood your question, post back.
    Attached Images Attached Images
    • File Type: gif x.gif (2.4 KB, 0 views)

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Primary Key question (Access XP)

    Like Hans, I'm confused by your question. A PrimaryKey can be composed of multiple fields. So, there is no need to combine the individual fields.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    New Lounger
    Join Date
    Oct 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key question (Access XP)

    Thanks for clarifying the PrimaryKey concept. I wasn't exactly aware that the Primary Key could be composed of multiple fields.

  5. #5
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key question (Access XP)

    While you can define compound PK's it's usually best not to do so. If you need a specific combination of fields to be indexed and unique this can be done without defining it as the PK. It's often far more desirable to have a single field for your PK (I prefer autonumber). You then need only have a single field FK in any tables that need to be linked to this one. By keeping your PK as a single field you will have a much easier time using subtables and queries.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Primary Key question (Access XP)

    However, it is often necessary to create a *unique* multi-field key anyhow in order to insure that you can get the records you need added to the database. The autonumber won't limit values in other fields, while the unique key will.
    Charlotte

  7. #7
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key question (Access XP)

    Yes, that's what I thought I said. Maybe I wasn't clear on that. It is often essential to have a unique multi-column index that will prevent the exact same combination of values for a given number of fields from being the same. But this combination of fields should not be used as a PK. It makes life just too difficult when creating joins between subtables to the multi-keyed one. A lot easier to use an autonumber as the table's PK. Of course, you can still use an autonumber field as a psuedoPK and leave the multi-column PK intact. Either way, it is easier to link tables using a single field than multiples.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Primary Key question (Access XP)

    It wasn't totally clear to me what you were saying, and I use autonumber PKs with multi-field unique keys all the time; so I thought it best to get clarification. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Charlotte

  9. #9
    New Lounger
    Join Date
    Oct 2002
    Location
    Chattanooga, Tennessee, USA
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Primary Key question (Access XP)

    If it wasn't clear to you then it wasn't clear to anyone else either. Thank you for pointing that out. I'm always one for insuring clarity. Though sometimes my brain gets ahead of my fingers.

    LOL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •