Query joins and Table relationships (1 Viewer)

[Hisoka]

When I created a query, the program by default created a relationship between my two tables In Query DesignView,
But when I opened the relationships from Database Tools > Relationships, I did not actually find this relationship and it does not exist in the tables either.
When I delete this relationship from the query DesignView, the program duplicates the data.
Why does the relationship appear in queries? Is it called an actual relationship or is it just for creating the query according to the user’s request or something Else?

Thanks for any tips and guidance

 

[jdraw]

Here's a link to an article that deals with this topic -Relationships vs Joins (MSAccess).
I have seen other posts that provide a description/explanation, but that link conveys the concept.

 

[Pat Hartman]

But when I opened the relationships from Database Tools > Relationships, I did not actually find this relationship and it does not exist in the tables either.

You won't necessarily see relationships if you open the Relationship diagram in the FE. What you see in the query is a Join, not a relationship. A relationship is physical and is enforced by checking the enforce RI box. Therefore, relationships can only be defined in a database between physical objects. You cannot define relationships in the FE on linked tables because the tables don't actually exist in the FE. They exist in the BE and the FE only links to them. You can draw join lines to remind yourself of the connections but they don't actually do anything.

Access likes to be helpful. Therefore, if there is a relationship in the BE, whether you can see it or not in the FE, Access will usually draw the join line for you and it will look like the relationship line. Access also sometimes helps by drawing join lines between columns with identical names.

 

[isladogs]

For additional information on this topic see

Relationships1

This article discusses the differences between query joins and table relationships and explains the important use of table relationships to enforce referential integrity. Three methods of adding relationships are discussed. The article also explains how Access uses the MSysRelationships system...

www.isladogs.co.uk