So many relationships

[njem]

Is there something that explains the interaction of various
relationships? I have a back end, and it has relationships defined. I
have a front end and found it had relationships defined. Not sure when
those showed up. This was originally a one-piece db that now is front
and back ends so who knows. Then some forms need relationships
defined. How do all these affect one another? And why if table A is
related to table B in the back end do I have to redifine that in order
to get that to work on a form? I've read what I can find on
relationships but didnt' find anything about this.

Thanks,
Tom

 

[Tony Toews [MVP]]

Is there something that explains the interaction of various
relationships? I have a back end, and it has relationships defined. I
have a front end and found it had relationships defined. Not sure when
those showed up. This was originally a one-piece db that now is front
and back ends so who knows. Then some forms need relationships
defined. How do all these affect one another? And why if table A is
related to table B in the back end do I have to redifine that in order
to get that to work on a form? I've read what I can find on
relationships but didnt' find anything about this.

Completely ignore the relationships in the FE. View and do all your
work with the relationships in the BE. Those are what count.

Additional information on relationships. If you import the tables
into a new MDB you will lose the relationships window layout. The
following tools will fix them up. (Although I don't know that they'll
work in A2007.)

Use the sysrels utility at the following location to copy the table
relationships layout window
http://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12
Or use Save Restore Modify Relationship Window at
http://www.lebans.com/saverelationshipview.htm

To print the relationships window

WindowToBitmap is a database containing a class to allow you to
capture and save the entire contents of the Relationship window to a
disk based Bitmap file. This is roughly a 4800 x 4800 pixel window.
The Bitmap is saved as a 16 color BMP in order to reduce file size.
http://www.lebans.com/windowtobitmap.htm

Hmm, I think there's another one somewhere else but I don't recall
where exactly now.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[Tom Wickerath]

The relationships you see in the front-end (FE) are most likely just
representations of the actual relationships in the back-end (BE), unless they
involve local tables in the FE. New queries that you create will inherit
these relationships, but the actual enforcement, with the Referential
Integrity option checked, occurs in the BE only.

Relationships with enforced Referential Integrity (RI) should pretty much
always be implemented. A relationship without enforced RI is little more than
an exercise in drawing lines.

Relationships can only have enforced RI when the tables are in the same
database. Thus, you can have relationships with enforced RI in the BE
database, and relationships with enforced RI between local tables in the FE
database, but you cannot have a relationship with enforced RI between a table
in the FE, and a table in the BE.

Then some forms need relationships defined.

Hum...the only "relationship" that one can define in a form, AFAIK, is the
Link Master Field / Link Child Field for a subform. Can you clarify more what
you mean by "some forms need relationships defined"?

And why if table A is related to table B in the back end do I have to
redifine that in order to get that to work on a form?

When you start with an unsplit application, as you did, and then you split
it, the only relationships that really matter reside in the BE database. The
relationships indicated in the FE are a representation only, but are used by
default for new queries, or forms created using more than one table (for
example, using the Form wizard to create a form with subform). Perhaps you,
or someone before you, deleted the representations of these relationships in
the FE application (or used the option in the Relationships view to hide a
table). Try right-clicking in the Relationships view, and then left-click on
the option to Show All. Then click on the Save button, before closing the
Relationships view.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[Armen Stein]

Hum...the only "relationship" that one can define in a form, AFAIK, is the
Link Master Field / Link Child Field for a subform. Can you clarify more what
you mean by "some forms need relationships defined"?

Hi Tom W, the OP may mean joins in the SQL statement in the
recordsource property. These really do seem like they're in the form.

Tom (OP), the lines that look like relationships in queries, or in
query properties in forms and reports, are really just joins. They
should default from your relationships as defined in your
relationships window in your BE in most cases. As joins, they govern
each particular query and can be overridden.

The most common thing to override from the default relationship is the
join type - that is, which way the arrow points. That determines
whether it is an Inner join or an Outer join, and if an Outer join,
which table is the main table.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[njem]

Use the sysrels utility at the following location to copy the table
relationships layout windowhttp://www.trigeminal.com/lang/1033/utility.asp?ItemID=12#12
Or use Save Restore Modify Relationship Window athttp://www.lebans.com/saverelationshipview.htm

Thanks for the links to the tools. I'll check them out.

Tom

 

[njem]

Hum...the only "relationship" that one can define in a form, AFAIK, is the
Link Master Field / Link Child Field for a subform. Can you clarify more what
you mean by "some forms need relationships defined"?

Right, when I refered to a form relationship I meant the link Master &
Link Child, which I think Armen was describing.

Thanks,
Tom

 

[njem]

Tom (OP), the lines that look like relationships in queries, or in
query properties in forms and reports, are really just joins.  They
should default from your relationships as defined in your
relationships window in your BE in most cases.  As joins, they govern
each particular query and can be overridden.

Okay, so forms and queries can have their own sets of relationships.
And you might want a form or query to have different relationships
than what's defined in the BE. I can understand that. But it's pretty
much just when you need to see the results of linking fields (in forms
or reports that display the query or datasource they're based on) that
relationships are called upon to do their job. So the relationships in
the BE are really just a suggested default? You could theoretically
have a BE with no relationships and just build relationships per query
that a form or report is going to use as its source? Not that you'd
want to, but just to wrap my head around this. Or is there some other
significance to the BE relationships?

When I dropped one form on another it didn't pick up the relationship.
As in the tutorial video it didn't know how to link the two until I
manually set that in the Link Master/Link Child properties. Curious
that it didn't pick that up. No problem though I just did that. But
here again an indication that relationships really only have
significance in the datasource of a form or report?

To take that thought a step further, regardless if I have RI and
cascades turned on in the BE, in the FE if I have one form in which I
have defined relationships (still dealing with tables in the BE) with
RI and cascades then changing data in that form will trigger cascades.
I could have another form in the same FE where I have not turned on RI
in its relationships and if I make changes in that form the resulting
cascades that should happen, won't?

Thanks,
Tom

 

[Armen Stein]

Right, when I refered to a form relationship I meant the link Master &
Link Child, which I think Armen was describing.

That's actually more what Tom W described. The master/child
relationship is usually with the same fields that participate in a
primary/foreign key relationship, but not always. In the form they
govern limiting and setting the child records to match the parent
records.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Okay, so forms and queries can have their own sets of relationships.

Well, not really.

The BE relationship window defines "relationships". Queries have
"joins". These normally default to the relationships defined in the
relationships window.

Forms and Reports can use tables directly, or queries (either saved
queries or SQL statements in query properties).

Forms also have Master/Child properties with subforms, which are kind
of like query joins, but they are a bit different in that they also
default the parent's foreign key for new records in the child subform.

You could theoretically
have a BE with no relationships and just build relationships per query
that a form or report is going to use as its source? Not that you'd
want to, but just to wrap my head around this. Or is there some other
significance to the BE relationships?

Yes there is! The Relationships window in the BE is the *only* place
where you define relationships between tables, and the referential
integrity of those relationships.

In queries, the relationships default in as *joins*. I know the lines
look the same, but they govern the join behavior only in that query.
They *don't* enforce referential integrity - that can only be done on
relationships in the BE.

When I dropped one form on another it didn't pick up the relationship.
As in the tutorial video it didn't know how to link the two until I
manually set that in the Link Master/Link Child properties. Curious
that it didn't pick that up. No problem though I just did that. But
here again an indication that relationships really only have
significance in the datasource of a form or report?

The BE relationships do a few things:

1) They document your overall database structure.
2) They enforce referential integrity between tables regardless of how
they are used.
3) They help default joins when the tables are later added to a query
in design view. The join type is also defaulted - inner or outer
join, along with the direction of the arrowhead for the outer join.

To take that thought a step further, regardless if I have RI and
cascades turned on in the BE, in the FE if I have one form in which I
have defined relationships (still dealing with tables in the BE) with
RI and cascades then changing data in that form will trigger cascades.

Nope. RI and cascades can only be defined in the BE relationships
window. Forms & queries can't do it.

I could have another form in the same FE where I have not turned on RI
in its relationships and if I make changes in that form the resulting
cascades that should happen, won't?

No. Again, the form can only control join behavior, not RI or
cascading.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

The BE relationship window defines "relationships". Queries have
"joins". These normally default to the relationships defined in
the relationships window.

There's also the Access option to AutoJoin (on the tables/queries
tab of TOOLS | OPTIONS in the main Access window). The What's This
text reads thus:

Select to automatically create an inner join between two tables
you add to the design grid. Clear if you want to define
relationships between the tables yourself. For the AutoJoin to
occur, the tables must each have a field with the same name and
data type and one of the join fields must be a primary key.

(did you know you could Ctrl-C when displaying What's This text and
it gets copied to the clipboard? I didn't, but tried it anyway, and
it worked!)

 

[David W. Fenton]

Forms also have Master/Child properties with subforms, which are
kind of like query joins, but they are a bit different in that
they also default the parent's foreign key for new records in the
child subform.

Actually, that's just like a join in a query, which will also fill
out the child foreign key if you set it up properly.

 

[njem]

Okay guys,

So one important piece is that RI can only exist in the db that has
the tables, and can only be established in the relationship, not, for
instance, when defining the connections between tables in a query.

I can see I missing one or two pieces for my pea brain to put this
together.

What is the difference between a relationship and a join? I know the
various types of joins and I get that. How is a join, say made in a
query, "just a join" and different from a relationship? I know that
each relationship, say in the BE, is also a join and has to have what
type of join it is spec'd. But how is that relationship more, or
different than "just a join" defined in a query?

Is it just the RI thing? That a join does not have the attribute of
RI? That a relationship is a join with the added feature of RI? Or is
there something else I'm missing?


Thanks all,
Tom

 

[Armen Stein]

Hi Tom,

You're asking great questions. I'm glad you're working on putting it
all together. You're not the only one who has been confused by this.

So one important piece is that RI can only exist in the db that has
the tables,
Yes.

and can only be established in the relationship, not, for
instance, when defining the connections between tables in a query.
Right.

What is the difference between a relationship and a join? I know the
various types of joins and I get that. How is a join, say made in a
query, "just a join" and different from a relationship?

The join in the query governs the behavior of that query *only*. It
has no effect outside that query. The Relationship governs the RI and
Cascading rules for those two tables everywhere.

I know that
each relationship, say in the BE, is also a join and has to have what
type of join it is spec'd. But how is that relationship more, or
different than "just a join" defined in a query?

A relationship *looks* like a join because it is shown as a line
connecting two tables. But really, it represents the relationship
between the tables, including the RI.

The fact that the Join Type is specified in the Relationship has often
caused confusion. It should actually be called *Default* Join Type.
It actually does *absolutely nothing* in the actual relationship. It
just defaults the join type into a new join when you add those two
tables to a query later.

Is it just the RI thing?

Pretty much. RI, Cascading, and default join fields and default join
type.

That a join does not have the attribute of
RI? That a relationship is a join with the added feature of RI? Or is
there something else I'm missing?

That's basically it. But a relationship isn't actually a join. It's
a relationship, possibly with RI enforced, that acts as a template
when you create a join later.

Hope this helps,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[John W. Vinson]

What is the difference between a relationship and a join?

A join is a temporary, ad-hoc connection between two tables. You can very
easily have several queries in your table with the same two tables joined -
they could be joined with an Inner Join in one query, a Left outer Join in a
second, a Right Outer Join in a third, and no join line at all (a Cartesian
join, pairing every record in the first table with every record in the second)
in yet another.

A relationship's main purpose is to enforce referential integrity: to ensure
that you cannot add a record to the "child" table unless a corresponding
record exists in the "parent" table. It's permanent and cannot be overriden by
changing a join in a query - you STILL can't add a record that violates RI, no
matter how you set up the query.

The directionality (left or right or inner) join feature of a Relationship is
just advisory - new Joins will default to having that directionality, but the
query can be changed or the join can be removed altogether, in the query; and
the directionality of the relationship does not affect the content of the
tables in any way.

 

[njem]

On Jan 17, 3:40 pm, Armen Stein <[email protected]>
wrote:

So these are all types of connections. All of these types of
connections have the common base that they define a field or set of
fields in each table that they connect on.
Beyond that:
A join also defines a kind of filter, whether all A and just matches
in B are returned, or only ones matched in A & B, etc.
The join (filter) really only exists in a query, which makes sense,
that's where you use it.
But the join does not define 1-1 or 1-many, and it cannot enforce RI
or execute cascades.
A relationship does not define that filter, since the filter doesn't
apply until you are going to use the connection in a query. (The
default is defined but as you say really has no effect.)
A relationship can enforce RI.
A relationship can execute cascades.
A relationship defines (or rather tells you) 1-1 or 1-many.
A relationship defines (or tells you) which is the primary table and
which is the related table.

I see that you don't get to choose 1-1 or 1-many, it simply tells you
what it is. It does this by looking at whether the dupe-keys-allowed
is set in either table?

I can also see that the direction of cascades is important, and it
also affects RI, so access needs to know which is the primary and
which is the "related" table. That's defined by the relationship box
where the left column is the "table" and the right column is the
"related table"?

A many (primary) to 1 (related) is not supported or considered valid
so it always sets the 1 to be primary and the many to be the related
table? (Choosing which is primary really only applies to 1-1.)


Thanks,
Tom

 

[David W. Fenton]

In a one-to-many relationship type the direction of a relationship
is determined by in which table the relevant column (or columns)
is the primary key (or at least a candidate key),

The 1 side doesn't have to be PK, it just has to have a unique
index. I don't know if it's possible to create a 1:N join with a
field on the 1 side that has a UNIQUE constraint but lacks a unique
index (the Access UI doesn't allow you to create a UNIQUE constraint
independent of an index, but it can be done with Jet/ACE DDL).

 

[David W. Fenton]

In mitigation, I did qualify my remarks with 'or at least a
candidate key', which I know doesn't necessarily mean its uniquely
indexed, so you are right to draw attention to what was somewhat
sloppy wording on my part.

Actually, I was trying to add in information not because I thought
your message was incomplete, but simply because I wanted to drive
home a distinction that I'd not realized existed until lately, i.e.,
that RI does not require a PK on the parent side, only a unique
index (and parhaps only a unique constraint).