Join tables and queries Access 2007
Author: mety Labels:: Join tables and queries Access 2007When you include multiple tables in a query, you use joins to help you get the results you are looking for. A join helps a query return only the records from each table you want to see, based on how those tables are related to other tables in the query. Relational databases consist, at the most basic level, of tables that bear logical relationships to each other. You use relationships to connect tables on fields that they have in common. A relationship is represented in a query by a join. When you add tables to a query, Microsoft Office Access 2007 creates joins that are based on relationships that have been defined between the tables. You can manually create joins in queries, even if they do not represent relationships that have already been defined. If you use other queries (instead of or in addition to tables) as sources of data for a query, you can create joins between the source queries, and also between those queries and any tables that you use as sources of data. Joins behave similarly to query criteria in that they establish rules that the data must match to be included in the query operations. Unlike criteria, joins also specify that each pair of rows that satisfy the join conditions will be combined in the recordset to form a single row. There are four basic types of joins: inner joins, outer joins, cross joins, and unequal joins. This article explores each type of join you can use, why you use each type, and how to create the joins. Inner joinsInner joins are the most common type of join. They tell a query that rows from one of the joined tables correspond to rows in the other table, on the basis of the data in the joined fields. When a query with an inner join is run, only those rows where a common value exists in both of the joined tables will be included in the query operations.Why would I use an inner join?Use an inner join if you want to return only those rows from both tables in the join that match on the joining field.How do I use an inner join?Most of the time, you don't need to do anything to use an inner join. If you previously created relationships between tables in the Relationships window (Relationships window: A window in which you view, create, and modify relationships between tables and queries.), Access automatically creates inner joins when you add related tables in query Design view (Design view: A window that shows the design of these database objects: tables, queries, forms, reports, macros, and data access pages. In Design view, you can create new database objects and modify the design of existing ones.). If referential integrity (referential integrity: Rules that you follow to preserve the defined relationships between tables when you enter or delete records.) is enforced, Access also displays a "1" above the join line to show which table is on the "one" side of a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.) and an infinity symbol (∞) to show which table is on the "many" side.Even if you haven't created relationships, Access automatically creates inner joins if you add two tables to a query and those tables each have a field with the same or compatible data type and one of the join fields is a primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.). The "one" and "many" symbols are not displayed in this case, because referential integrity is not enforced. If you add queries to your query, and have not created relationships between those queries, Access does not automatically create inner joins between those queries or between queries and tables. Generally, you should create them yourself. You create an inner join by dragging a field from one data source to a field on another data source. Access displays a line between the two fields to show that a join has been created.  SQL syntax for an inner joinInner joins are specified in SQL in the FROM clause, as shown below: FROM table1 INNER JOIN table2 ON table1.field1compopr table2.field2 The INNER JOIN operation has these parts:
Outer joinsOuter joins tell a query that although some of the rows on both sides of the join correspond exactly, the query should include all of the rows from one table, and also those rows from the other table that share a common value on both sides of the join.Outer joins can be left outer joins or can be right outer joins. In a left outer join, the query includes all of the rows from the first table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables. In a right outer join, the query includes all of the rows from the second table in the SQL statement FROM clause, and only those rows from the other table where the joining field contains values common to both tables. Note You can easily tell which table is the left table or the right table in a given join by double-clicking the join and then looking in the Join Properties dialog box. You can also switch to SQL view, and then examine the FROM clause. Because some of the rows on one side of an outer join will not have corresponding rows from the other table, some of the fields returned in the query results from that other table will be empty when the rows do not correspond. Why would I use an outer join?Use an outer join if you want all of the rows from one of the tables in the join to be included in your results and you want the query to return only those rows from the other table that match the first table on the joining field. How do I use an outer join?You create outer joins by modifying inner joins. Change an inner join to an outer join
Outer joins are specified in SQL in the FROM clause, as shown below: FROM table1 [ LEFT RIGHT ] JOIN table2 ON table1.field1compopr table2.field2 The LEFT JOIN and RIGHT JOIN operations have these parts:
Cross joinsCross joins are different from inner and outer joins in that they are not explicitly represented in Office Access 2007. In a cross join, each row from one table is combined with each row from another table, resulting in what is called a cross product or a Cartesian product. Any time you run a query that has tables that are not explicitly joined, a cross product is the result. Cross joins are usually unintentional, but there are cases where they can be useful. Why would I use a cross join?If you want to examine every possible combination of rows between two tables or queries, use a cross join. For example, suppose your business has had a spectacular year, and you are considering giving rebates to your customers. You can build a query that sums each customer's purchases, create a small table that has several possible rebate percentages, and combine the two in another query that performs a cross join. You end up with a query that displays a set of hypothetical rebates for each customer. How do I use a cross join?A cross join is produced any time you include tables or queries in your query and do not create at least one explicit join for each table or query. Access combines every row from each table or query that is not explicitly joined to any other table or query to every other row in the results. Consider the rebate scenario from the preceding paragraph. Assume you have 91 customers, and that you want to look at five possible rebate percentages. Your cross join produces 455 rows (the product of 91 and 5). As you might imagine, unintentional cross joins can create huge numbers of rows in your query results. Moreover, these results are generally meaningless, because if you don't actually intend to combine every row with every other row, most of the combined rows that appear in the results will not make sense. Finally, queries that use unintentional cross joins can take a very long time to run.  An unintentional cross join in query Design view.  The circled fields should be joined to each other. The cross product returned by the unintentional cross join depicted above. Note the very large number of records. The results after the correct join is created. Note that the number of records is much smaller.Unequal joinsJoins do not have to be based on the equivalence of the joined fields. A join can be based on any comparison operator, such as greater than (>), less than (<), or does not equal (<>). Joins that are not based on equivalence are called unequal joins. Why would I use an unequal join?If you want to combine the rows of two sources of data based on field values that are not equal, you use an unequal join. Typically, unequal joins are based on either the greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (<=) comparison operators. Unequal joins that are based on the does not equal (<>) operator can return almost as many rows as cross joins, and the results can be difficult to interpret. How do I use an unequal join?Unequal joins are not supported in Design view. If you wish to use them, you must do so by using SQL view. However, you can create a join in Design view, switch to SQL view, find the equals (=) comparison operator, and change it to the operator you want to use. After you do this, you can only open the query in Design view again if you first change the comparison operator back to equals (=) in SQL view. Removing a joinIf you create a join by mistake, for example, a join between two fields that have dissimilar data types, you can delete it. To delete the join:
-or-
|
