Table level relationship vs query level join

[cpberg1]

Hey all,

New to exploring access and having fun doing it! I want to learn more about the difference between defining a relationship on the relationship window vs. Doing it only as a join in the query.

I gather that from the relationship area it will apply to all future queries using those tables and if only done it a query could obviously be not included next time.

If the fields are related and have a unique join type, why not create the relationship?

I'm probably oblivious to something obvious but like I said, I'm new here. Clear replies are definitely appreciated!

 

[jdraw]

This is an aspect of your question that occurs more often than one would think.
http://access.mvps.org/access/lookupfields.htm

If you do a proper database design, 3NF, then relationships would seem the way to go.

Tables structure with proper relationships are key to a useful and efficient database - and simplify many queries and data manipulation.

 

[gemma-the-husky]

I take it you do NOT mean declaring a lookup in the table

If you can establish a relationship between tables, it simplifies subsequent use of those tables within queries. If you define relational integrity, it also can protect your data aganist invalid entries.

also, defining a relationship causes access to build an (hidden) index on the FK field, which may speed up query extraction.

no harm in establishing the relationship therefore, but equally, everything can be done manually within queries.

 

[Galaxiom]

Since a relationship creates a hidden index, should one not explicitly add an index to those fields because it would create an unnecessary overhead?