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

[Modify_inc]

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

[orange]

Perhaps, something like this

Renter--->Agreement<----Condo
                          +
                          |
                      Location

[Modify_inc]

After much trial and error, I believe that will have to work.

I was trying to design it so a different renter couldn't rent a condo that was already rented by another renter. Currently, a renter can rent a condo even if it has already been rented. I assume this can't be resolved by the table relationships alone.

[orange]

Not unless you work out the appropriate Business Facts

-A Condo is situated at a specific Location
-A Renter rents 1 or more Condo
-An Agreement identifies the Renter, the Condo and the MonthlyRentalFee
-An Agreement has a StartDate and an EndDate
-A MonthlyRentalFee is charged to the Renter

A Renter may consist of 1 or many People

[June7]

Why would one agreement have many condos?

Preventing a condo from being associated simultaneously with two parties will require other data validation methods - similarly to checking out a book in the Lending Library db template.