Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2013
    Posts
    14

    Unanswered: Can't swap table sides in a one-to-many relationship

    Using Access 2013, what would cause a one-to-many relationship to work ONLY in one direction? Say from Table A to Table B, and when I attempt to change the direction of the relationship, so that is should be Table B to Table A, it doesn’t work. I don’t get an error, but Access reverts any changes I make while in the Relationship Design.

    I have four tables: Renter, Location, Condo, and Agreement.

    RENTER (RENTER_NUM, FIRST_NAME, LAST_NAME, ADDRESS, CITY, STATE, ZIPCODE, TELEPHONE, EMAIL)

    LOCATION (LOCATION_NUM, LOCATION_NAME, ADDRESS, CITY, STATE, ZIPCODE)

    CONDO (UNIT_NUM, LOCATION_NUM, SQR-FT, BDRMS, BATHS, WEEKLY_RATE, RENTER_NUM)

    AGREEMENT (RENTER_NUM, UNIT_NUM, START_DATE, END_DATE, WEEKLY_FEE)

    I can link RENTER to AGREEMENT using the RENTER_NUM field, which enables one renter for multiple agreements. I can link CONDO to AGREEMENT using the UNIT_NUM field, which enables one condo for many agreements, but I need AGREEMENT to be the one side and CONDO the many.

    The problem is Access will lead me to believe I can change the sides, but when I create it after making my selections in the Relationship Design window, it reverts back, and doesn’t even give me an error or a reason why. I closed out of Access and tried it again but same thing.

    Could it be related to the AGREEMENT table using two foreign keys as primary keys? If so, why?

    Mike

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,423
    Provided Answers: 8
    is the RENTER_NUM the PK in the RENTER

    does the AGREEMENT table have a PK it cant be the Renter_NUM

    When im build tables I do this
    name the PK the same name as the table with ID on the End

    then just by looking at the Tables you can see what it relate to in other tables

    something like

    [Renter]
    RenterID
    AGREEMENTID
    FIRST_NAME
    LAST_NAME
    ADDRESS
    CITY
    STATE
    ZIPCODE
    TELEPHONE
    EMAIL

    [LOCATION]
    LOCATIONID,
    LOCATION_NAME
    ADDRESS
    CITY
    STATE
    ZIPCODE

    [CONDO]
    CONDOID
    UNIT_NUM
    LOCATIONID
    SQR-FT
    BDRMS
    BATHS
    WEEKLY_RATE
    RENTERID

    [AGREEMENT]
    AGREEMENTID
    UNIT_NUM
    START_DATE
    END_DATE
    WEEKLY_FEE
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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