Making a relationship One-To-Many

[GraeagleBill]

I made a recent change to one of my table relationships. In the screenshot below, the relationship between "FamilyGrpID" in table on the left should be "One-To-Many" with "GroupID" in the Groups table. I can't find where to change the relationship type from "Intermediate" to "One-To-Many". What am I missing?

(BTW, the table on the left is named "Families")

[ranman256]

If you only have 1 indexed field, then its 1 to 1.
If 1 family can have Many groups, then the tGroup table needs a 2nd key (or index).

[GraeagleBill]

I don't quite follow. Right now, I have a form whose RecordSource is the Families table (actually a query of that table). On that form currently, there's a text box that is bound to the GroupName field in table Groups via a DLookup control source. I.e., =DLookUp("GroupName","Groups","GroupID = " & FamilyGrpID). Right or wrong, I had the idea that if the relationship between the two was established that I could simply bind the control directly to "GroupName" and Access would find it. If that's a false notion on my part then I'll let the DLookup stand. But still, given an ID FamilyGrpID, there can theoretically be an infinite number of GroupNames to chose from. Where do I assign the index in the Groups table?

[ssanfu]

Is the field Groups.GroupID an Autonumber?
Is the field Families.FamilyGrpID Numeric (Long Integer)?

Or are both field the same data type?


If you double click the link between table Families and table Groups, does the "Edit Relationships" dialog box appear with "Enforce Referential Integrity" (RI) enabled?
If YES, click the check box to the left of "Enforce Referential Integrity". Tada!


If Access won't allow you to enable RI, then you might have entered a value in "Families.FamilyGrpID" that is NOT in "Groups.GroupID".

[theviking]

As ranman mentioned, you need to have an index (or key) on the GroupID which allows duplicate values.

[Gicu]

GraeagleBill,

I think what you need is to replace the text box on the form with a combo-box; its control source should be FamilyGrpID and its RowSource "Select GroupID, GroupName From Groups Order By GroupName;". Set its column count to 2 and the column widts to 0';2' (or whatever you need to accommodate your group names). Now when you select a group name in the combo it will save its GroupID in your Families table in the FamilyGrpID field.

Cheers,
Vlad

[GraeagleBill]

combo not applicable with the current app. (But that is how the GroupID is initially obtained.)

[ssanfu]

Did you try Post#4?

[GraeagleBill]

I replied to #4 yesterday but I don't see it within the thread???
Anyway,

Is the field Groups.GroupID an Autonumber?
Is the field Families.FamilyGrpID Numeric (Long Integer)?

YES

Or are both field the same data type?

YES

does the "Edit Relationships" dialog box appear

YES, as pictured in the OP. As you can see, "Enforce Referential Integrity" is not checked and nothing I attempt to do with the "Edit" menu has any effect.

[ssanfu]

I looked to see if I had a copy of your dB, but didn't find one.
Would you make a copy of the dB, delete all records, do a "Compact and Repair", compress (zip) it and post it?

[GraeagleBill]

I appreciate your willingness to examine the app but it's really big and more work to package up for this issue than I have time for. (Over 5K LOC, 60 Forms, 70+ queries, reports, tables, blah, blah blah)
Thanks,
Bill

[ssanfu]

Delete the records, forms, queries, reports, modules and macros (if any). Just keep the tables.

Trying to see why you can't set RI on tblGroups per image in Post #1.......

[GraeagleBill]

I'll see if I can make time on Friday or Saturday to configure a DB you can play with.