Join Types
If the query is based on more than one object there needs to be a relationship between the different objects other wise the result of the query will be meaningless.
This query shows that there is a relationship between the tables Products and Suppliers, so it will only show the supplier information for the products records that are there. As the join is equal, if there are any products that do not have the supplier information filled in then it will not show those products.
As long as the data is complete in all the fields then the number to records returned by this type of query should equal the number of records in the larger table, but with incomplete data it could be less than the number of records in the larger table.
If the join between the two tables is removed then the query will assume that every product in the products table is being supplied by every supplier in the suppliers table. The resultant dynast than has a multiple number of records from two tables.
There are not many occasions where this combination is required and if this is done between two large tables, the query will run very slowly. This dynaset was generated by tables of seventy seven and twenty nine records – imagine the size when the table sizes are in the tens of thousands!
Changing the Join Types
When more than one table is added to a query and there are relations between the tables, if the join types have not been changed elsewhere then the default relationship is where the two sides are equal.
Depending on the data involved, the join types might need to be changed.
The join type can be changed by double clicking on the line that joins the two tables together. This brings up the join properties.
There are three options
1. Only include rows where the joined fields from both tables are equal.
2. Include all records from Left Table and only those records from Right Table where the joined fields are equal.
3. Include All records from Right Table and only those records from Left Table where the joined fields are equal
The first option will show the products and their suppliers. (Only for those products that have supplier information filled in.)
The second option will show all the products with the corresponding supplier information where it has been filled in.
The third option will show all of the suppliers with their products. This could include Suppliers where they do not have an associated product.
No comments:
Post a Comment