?
Solved

Access Relationship Problem

Posted on 2015-01-19
7
Medium Priority
?
214 Views
Last Modified: 2015-01-19
I have an exercise in which I need to assign one-to-many relationships to some tables in a db.

I can get one such relationship but all the remaining tables form a one-to-one relationship and I don't understand why when they are very similar to the two tables in which I was able to form a one-to-many relationship.

The primary keys have the same data types. In my file a one-to-many relationship exist between the table "tblRentals" and tblRenters". I need to form the same relationship between all the remaining tables except that when I try I get a one-to-one relationship. Why???
0
Comment
Question by:James Coats
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 40558281
Do you have the Primary Key in the One table as a Foreign Key in the Many table ... and for the Foreign Key ... do you have Index - Allow Duplicates ?

mx
0
 
LVL 58
ID: 40558283
For a one to many, you should have a copy of the "one's" primary key in the record of the "many" sides record as a separate field.  This is called a foreign key.

If your getting a one to one, then what your doing is joining the primary keys of each table to one another, which is not what you want.

Jim.
0
 
LVL 75
ID: 40558338
:-)
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Closing Comment

by:James Coats
ID: 40558354
I went to the "tblRentals" and selected the key or foreign key for each of the table and selected "Allow Duplicates" from the connecting tables primary keys which now allows a one-to-many connections. I also enforced referential integrity. Thanks for the help. You both answerd correctly but you got there first so I gave you the points.

So the way I see it now on the table connecting you select a primary key you choose no duplicates for the table and then you choose the table being connected to and place your foreign key there and select allow duplicates. You also would have indexed this field. Is this correct??
0
 
LVL 58
ID: 40558361
<<You also would have indexed this field. Is this correct?? >>

 If you enforce Referential Integrity, no as Access (actually JET) will create an index on the many side of the join.

 If you create an index with RI enforced, you'll be creating a duplicate index.   Note that you won't see the hidden index.

Jim.
0
 
LVL 75
ID: 40558422
Actually ... I really didn't mean to say (and Jim is correct about the dup index) "and for the Foreign Key ... do you have Index - Allow Duplicates ?" .... what I meant was ... be SURE that you do not have Indexed - No Duplicates - which will result in a One to One when you try to create a Relationship.


And of course, unless you enforce RI ... you don't really have a meaningful Relationship.

mx
0
 

Author Comment

by:James Coats
ID: 40558435
Thanks a lot guys. I am new and learning a lot.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question