The use of table relationships in Access allows tables to cooperate in the use of data, but table joins, while they look similar, serve a different purpose. The relationships, which allow coordination while organizing data, nevertheless don’t directly affect, say, the results of a query. Joins do.
A table join in a query allows for a filter effect. The thinking is that a query is a question one asks of the database. But redundant answers are no use, and a waste of space. So when we pull data from more than one table, which is frequently the case, we need to make sure we don’t get repeat answers, or every possible permutation of the answers/data.
When creating the query, we choose the tables (assuming we need more than one, which is normal) and insert them.
If we run the query without a join, we get every possible combination of the field data for an answer…which often leads to repeat results.
So we decide which field(s) to use in the join (this is often dictated by the nature of the database), and specify the nature of the join—usually an “inner” join, which shows only results both tables share. We create the join the same way as we create a relationship, by dragging from a field in one table to its counterpart in the other—the main difference to the program is, the join only gets “activated” when the query is run, saving time and memory usage. There’s also the fact that some joins are only useful at certain points in the database’s operation, so creating a permanent relationship outside the query is unnecessary.
When we run the query with the join, to filter out repeats, the number of answers becomes more reasonable. In many cases, we can use a key field as one of the query result fields, which certainly cuts down on repeats, as a key field has the same effect in a query as it has in a database in general—unique identification of a record. But the important point is that table joins will “insist” on reducing if not eliminating repeats, and will do so even more with a key field involved.
It’s been my experience that very few business database-related queries will have no repeat results at all—the data are such that this seems almost impossible. But if we can eliminate 95+% of repeat data in a query, only the most massive sets of results will have a significant problem with them.