June 5, 2008 at 2:43 pm
When I add tables to the Relationships Layout area, I see there are some already pre-determined relationships drawn from me. They are linking Indexed fields. One of them is the Primary key index. The tables are linked together by this Primary key, but the relationship is inderminate. Question: Why is Access drawing these relationships by default? Is there really any benefit to having "indeterminate" relationships between tables? The only utility of having a Primary key on the tables is to establish that each record in that column is unique (and to speed indexing). But Access by default draws tables with the same PKs to the same fields in other tables. What would be the beneifit to keeping these "indeterminate" relationships? Thanks
June 6, 2008 at 2:18 am
I think that if Access spots fields with the same name in two tables it helpfully decides to join them for you. If your primary key fields are called the default name 'ID' then this may explain what is going on.
I am not sure what you mean by 'indeterminate' in Access terms though - could you enlighten me please?
June 10, 2008 at 2:14 pm
~paul hewitt (6/6/2008)
I think that if Access spots fields with the same name in two tables it helpfully decides to join them for you. If your primary key fields are called the default name 'ID' then this may explain what is going on.I am not sure what you mean by 'indeterminate' in Access terms though - could you enlighten me please?
That seems to be the case. In the Relationships view in Access, the relationships are drawn between fields with the same name. The field name never appears to be modified with the 'ID' attribute, however. Just these benign relationships are drawn, all without any significant purpose, except, perhaps, to aid in showing the similiaries between the tables.
You can identify indeterminate relationships when you goto to the Relationships window. Open up as many tables as you want by right-clicking the area and choosing "Show Table". You'll have tables that populate the area. Many of the ones I choose have the same field name, and the black line is drawn between them. Right-click on the Black line which links the identical field names. (some are not, too!) Choose "Edit Relationship". Then in the dialog box, at the bottom, will be a description "Type of Relationship", and the property will be "indeterminate". In this dialog box, Access gives you the option to make it a one-to-many or one-to-one, but I cannot choose these options because the indicies on these fields were set up to allow duplicates. Is this why the default "indeterminate" is appearing? I dunno.
June 11, 2008 at 2:10 am
Weird! I've never noticed that happening before, maybe because I usually work with linked sql tables rather than Access tables. I guess you can just delete these automaticly created relationships then set up the correct joins according to your design.
June 11, 2008 at 6:43 pm
~paul hewitt (6/11/2008)
Weird! I've never noticed that happening before, maybe because I usually work with linked sql tables rather than Access tables. I guess you can just delete these automaticly created relationships then set up the correct joins according to your design.
My thinking is that perhaps Access is merely showing you what fields your tables have in common, draws lines between them, and "suggests" that you provide explicit relationships. DB Books, I presume, would say "indeterminate" relationships is a violation of a pillar of good coding: reusability.
June 13, 2008 at 4:54 pm
You have the auto-join option turned on, and Access is guessing the relationships based on field names & data types.
Click Tools ~ Options and click the Tables/Queries tab. Un-check the box Enable Auto-Join.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply