A distinction between 'relationship' and 'join' should be made. Most of time we do join tables based on an existing relationship, but you don't actually have to have relationship to join tables nor do you need tables to be joined to have relationship.
Relationship is basically a constraint. It's telling Access "You can't add the record in that table if it doesn't have the values that you can find in the table it's related."
Join is basically telling Access "Please get me all records in that table where the certain column's value matches the other table's column value."
The reason why we most often do join that's based on an existing relationship is to ensure we're using indexes to make the join operation faster.
With relationship window, it serves two function; 1) to create a relationship and 2) to show the relationship. It does not show any information on joins.
That said, if we were to issue the SQL DDL statement (You can try it for yourself by creating a new blank query, select View -> SQL View and copy & pasting the statement):
(Remember that relationship is basically a constraint referencing another table; the statement is saying that the ChildID field is constrained by referencing a table named foo (which is itself, of course) with the ID field being the foreign key.)
When the statement is executed and relationship windows is opened, you'll see a duplicate copy of one table with the line between the ID and ChildID.
I hope this helps a bit in seeing how things work.
One small clarification ... when Banana used the term "Relationship" ... it really should be "Relationship With Referential Integrity". A "Relationship" by it self, pretty much just tells Access how to "AutoJOIN" two tables when they are brought into the Query Designer ...
Many times a developer will use the term Relationship and assume that RI (Referential Integrity) is enforced ... kinda like when we say "Click This" we mean "Left Click This"... I just wanted to add that little bit to the great explanation by Banana.
Ah, yes. I clean forgot about the RI. All time I make a new relationship, I click the RI, without even thinking about it. Thanks for that clarification!
The base question is whether a relationship should be defined in the relationship window or the query-grid window.
I use these rules:
1. If the relation that would have gone into the query grid won't interfere with any other existing relation, declare it in the relationship window. That documents it better. Even if the relationship applies to one and only one query.
2. If and only if the relationship is contrary to existing relationships, declare it in the query design window above the grid.
3. If any contributor recordset of the query being designed is itself a query, you MUST define the relationship in the query design window.
The thing to watch out for is the word "contrary" in #2. It is very hard to imagine an exemplar of this case that doesn't involve normalization violations, so in most cases #2 is not an issue. I'm not sure that this next example can even happen in Access, but it might be possible.
Suppose that table A, when you designed it, had TWO candidate keys. You had to choose only one of them. Table B somehow has both candidates key fields as well. So you picked field 1 in both. But if your non-standard query needs to use field 2 in both, I don't think you want a permanent relationship that uses field 2. You want your selected PK used for the generally defined relationship. In this case, though, you might be able to define a spot relationship between the fields that were NOT chosen as PKs. The table on the ONE side would need an index on that field to make it work.
Having described the only situation I could imagine where you would need it, I think that relationship is technically in violation of normalization. As a matter of pragmatism, I have been known to explore oddball relationships. (In Access.... ;-) )
Anyway, the best answer to your original question is therefore, put your relationship in the relationship window if at all possible.
