08-29-15, 16:52 #1Registered User
- Join Date
- Feb 2013
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?
08-30-15, 15:55 #2(Making Your Life Easy)
Provided Answers: 10
- Join Date
- Feb 2004
- New Zealand
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
WEEKLY_FEEhope this help
the aim is store once, not store multiple times
Remember... Optimize 'til you die!
Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
VB-NET based on my own environment started 2007
SQL-2005 based on my own environment started 2008
YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
THEY'RE BEHIND YOU FOR A REASON