How useful are linked lists

Link tables and queries

When including multiple data sources in an Access query, use joins to limit the records that you want to display based on how the data sources are related. You also use joins to combine records from both data sources so that each pair of records from the sources becomes a record in the query results.

This article explains the different types of joins and how to use them in a query. By default, a join is created automatically if there is already a relationship between two data sources that you are using in a query. A link is also created if there are fields that clearly match each other. You can delete an automatically created shortcut. This article provides basic information about table relationships, including how to create a table.

Note: You can join queries in the same way as tables, and you can join both of them.

Content of this article

Overview

A database is a collection of data tables that have logical relationships with one another. You use relationships to join tables based on fields they have in common. A table can be part of any number of relationships, but each relationship always contains exactly two tables. In a query, a relationship is represented by a link.

When you add tables to a query, Access creates joins based on relationships 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 data sources for a query, you can create joins between the source queries and also between those queries and any tables that you use as data sources.

Joins behave similarly to query criteria in that they establish rules that the data must conform to in order to be included in the query operations. Unlike criteria, joins also indicate that any pair of rows that meet the join conditions will be combined into a single row in the recordset.

There are four basic types of links: inner links, outer links, cross-fill, and unequal links. This article explains each of the types of shortcuts you can use, why you use each type, and how the shortcuts are created.

Joins are designed to query what relationships there are with tables: an indication of how data in two sources can be combined based on common data values. Here is an illustration of a link in Query Design view with the link's properties open in a dialog box:

This line between the tables represents the link. Double-click a link to open the Link Properties dialog box (shown) and review or change the link.

Links are sometimes directional. This area of ​​the dialog box shows which table is included in the join and which fields are used to join the tables.

This area determines the type of link: Option 1 is an inner link, 2 is a left outer link, and 3 is a right outer link.

Fields from either table can be used, and data related to a specific task is displayed from each table. No other data is contained in an inner join. In an outer join, unrelated records from a table are also included in the query results.

Top of page

Link types

There are four basic types of links: inner links, outer links, cross-fill, and unequal links. Cross joins and unequal joins are advanced join types and are rarely used, but you should know about them to have a full understanding of how joins work.

Inner links: Only linked data from both tables combined

An inner join is a join in which Access only contains data from a table if there is corresponding data in the linked table, and vice versa. Most of the time you use inner links. If you create a link and don't specify what type of link it is, Access assumes that you want an inner link. Inner joins are useful because they allow you to combine data from two sources based on shared values ​​so that data is only displayed when a full picture is displayed.

Outer joins: All related data combined properly, plus any remaining records from a table

An outer join is like an inner join, but adds the remaining rows from one of the tables. Outer joins are directional: a left outer join contains all records from the left table - the first table in the join - and a right outer join contains all records from the right table - the second table in the join.

Full outer links: all data, combined where possible

In some systems, an outer join can contain all of the rows from both tables, combining rows if they match. This is known as a full outer join, which Access does not explicitly support. However, you can use a cross-lock and criteria to get the same effect.

Crossing links: all data, combined in every possible way

Most of the time, a cross join is a side effect of adding two tables to a query and then forgetting to join them. Access interprets this to mean that you want to see every record from one table along with every record from the other table - every possible combination of records. Since no data can be combined, this type of join seldom produces useful results. However, there are some cases when a cross-lock is exactly what you need.

Unequal links: like a normal link, but with a different comparison for combining rows

Unequal joins use an operator other than the equal sign (=) to compare values ​​and determine whether and how the data is combined. Mismatched joins aren't explicitly supported, but you can use a cross-lock and criteria to get the same effect.

Show rows that have a common value in both of the related tables

If you only want to display the rows that have matching values ​​in the merged field, use an inner join. Access automatically creates inner links.

Inner links are the most common type of link. You tell a query that rows from one of the related tables match rows in the other table, based on the data in the related fields. When a query is run with an inner join, only those rows that have a common value in both of the joined tables are included in the query operations.

How do I use an inner link?

Most of the time, you don't have to do anything to use an inner link. If the tables you add to a query already have relationships, Access automatically creates an inner join between each related pair of tables as you add the tables. When referential integrity is activated, Access displays a "1" above the link line to identify the table on the 1 side of a 1: n relationship and the symbol for infinity () to identify the table on the n-side.

Even if you haven't created any relationships, Access automatically creates inner joins when you add two tables to a query and those tables each have a field of the same or compatible data type and one of the join fields has a primary key. 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 them, Access does not automatically create inner joins between these queries or between queries and tables. In general, you should create them yourself. You create an inner join by dragging a field from one data source to a field in another data source. Access indicates that a link has been created with a line between the two fields.

SQL syntax for an inner join

Inner joins are specified in SQL in the FROM clause, as shown below:

FROM Table 1 INNER JOIN Table 2 ON Table 1.Field1 compares table2.Field2

The INNER JOIN process is composed as follows:

part

description

Table 1, Table 2

The names of the tables that will be used to aggregate records.

Field1, Field2

The names of the fields that are connected. If they are not numeric, the fields must have the same name data type and type of data, but they do not have to have the same name.

comparison

Any relational comparison operator: "=", "<", ">", "<=", "> =" or "<>".

For more information about the inner join syntax, see the topic INNER JOIN operation.

Top of page

Display all rows from one table and corresponding rows from the other table

Outer joins tell a query that while some of the rows on both sides of the join match exactly, the query should contain all of the rows from one table and also the rows from the other table that have a common value on both sides of the join.

Outer links can be left outer links or right outer links. In a left outer join, the query contains all rows from the first table in the SQL statement FROM clause and only the rows from the other table in which the join field contains values ​​that are common to both tables. In a right outer join, the query includes all rows from the second table in the SQL FROM clause and only the rows from the other table in which the join field contains values ​​that are common to both tables.

Note: You can easily see which table is the left or right table in a particular link by double-clicking the link and then looking in the Link Properties dialog box. You can also switch to the SQL view and then examine the FROM clause.

Because some of the rows on one side of an outer join do not contain corresponding rows from the other table, some of the fields returned in the query results from that other table will be blank if the rows do not match.

How do I use an outer join?

You create an outer link by modifying an existing inner link. If there is no inner link, create a link and then change it to an outer link.

Change an inner link to an outer link

  1. In Query Design view, double-click the shortcut that you want to change.

    The dialog box Link properties is displayed.

  2. Write down in Link Properties dialog box, the options next to Option 2 and option 3 are listed.

  3. Click the option you want, and then click OK.

  4. Access displays the join and an arrow pointing from the data source that includes all rows in the data source, including only those rows that meet the join condition.

Ambiguous outer links

When you build a query that includes a LEFT JOIN and an INNER JOIN, Access may not be able to determine which join operation to perform first. Because the results are different depending on whether the left or inner join runs first, Access displays an error message:

To fix this error, you need to modify the query so that it is clear which link to perform first.

SQL syntax for an outer join

Outer joins are specified in SQL in the FROM clause, as shown below:

FROM Table 1 [LEFT | RIGHT] JOIN table2
ON table1.field1 compare table2.field2

The LEFT JOIN and RIGHT JOIN operations consist of the following parts:

part

description

Table 1, Table 2

The names of the tables that will be used to aggregate records.

Field1, Field2

The names of the fields that are connected. The fields must have the same name, data type and type of data, but they do not have to have the same name.

comparison

Any relational comparison operator: "=", "<", ">", "<=", "> =" or "<>".

For more information about the outer join syntax, see the LEFT JOIN, RIGHT JOIN Operations topic.

Top of page

Show all rows from both tables and connect them where there is a common value

If you want to display all the rows from two tables and join them based on common values, use a full outer join. Access doesn't explicitly support full outer joins, but you can get the same effect by using a union query. The following procedure explains how to do this. However, if you want more information about union queries, see the section See also.

To use a union query to perform a full outer join:

  1. Create a query with a left outer join for the field you want to use for a full outer join.

  2. Click the tab begin in the group Sifting on view and then on SQL view.

  3. Press Ctrl + C to copy the SQL code.

  4. Delete the semicolon at the end of the FROM clause, and then press ENTER.

  5. Give UNION , and then press ENTER.

    Note: Do not use the ALL keyword when using a union query to perform a full outer join.

  6. Press Ctrl + V to paste the SQL that you copied in step 3.

  7. Change in the code you are using LEFT JOIN in RIGHT JOIN.

  8. Delete the semicolon at the end of the second FROM clause, and then press ENTER.

  9. Add a WHERE clause indicating that the join field value is NULL in the first table listed in the FROM clause (the table on the left).

    For example, if the FROM clause B. the following is:

    You add the following WHERE clause:

    WHERE Products.ID IS NULL

  10. Include a semicolon (;) at the end of the WHERE clause to indicate the end of the union query.

  11. Click the tab design in the group Results on To run.

Cross crossings

Cross-fillings are different from inner and outer joins in that they are not explicitly used in Access. In a cross-locking, each row from one table is combined with each row from another table, resulting in what is known as a cross product or a Cartesian product. Any time you run a query that contains tables that are not explicitly related is a Cross-Product Result. Cross-crossings are usually unintentional, but there are times when they can be useful.

Why should I use a cross lock?

If you want to examine every possible combination of rows between two tables or queries, use cross-locking. For example, let's say your company is having a spectacular year and you are considering giving discounts to your customers.You can create a query that sums up each customer's purchases, creates a small table with several possible discount percentages, and combines the two in another query that cross-closes. At the end, a query is displayed that shows a number of hypothetical discounts for each customer.

How do I use a cross lock?

A cross-lock is created every time you include tables or queries in your query and don't create at least one explicit join for each table or query. Access combines every row from every table or query that is not explicitly linked to another table or query with every other row in the results. Take into account the discount scenario from the previous paragraph. Suppose you have 91 customers and you want to look at five possible discount percentages. Your cross-closure produces 455 rows (the product of 91 and 5).

As you can imagine, accidental cross-overs can generate large numbers of rows in the query results. In addition, these results are generally meaningless, as most of the combined rows that appear in the results don't make sense unless you intend to combine each row with every other row. Finally, queries that use unintended cross joins can take a long time to run.

1. The circled fields should be connected to each other.

1. Note the very large number of data records.

1. Note that the number of data records is much smaller.

Top of page

Join tables based on an inequality of field values

Links do not have to be on the equivalence of connected fields are based. A join can be based on any comparison operator, e.g. B. greater than (>), less than (<), or not the same (<>). Links that are not based on equivalence are called unequal links.

If you want to combine the rows of two data sources based on field values ​​that are not the same, use an unequal join. Unequal links are usually based either on the comparison operators greater than (>), less than (<), greater than or equal to (>=), or less than or equal to (< =) Comparison operators. Unequal links that are not equal on the operator (<>) can return almost as many rows as crosslinks, and the results can be difficult to interpret.

How do I use a mismatched link?

Mismatched links are not supported in Design view. If you want to use it, you need to do it using the SQL. However, you can create a link in Design view, switch to SQL view, use the comparison operator equal to (=) and change it to the operator you want to use. You can then only open the query again in design view if you first use the comparison operator in the view equal to (=) Change SQL.

Delete a link

If Access automatically creates a shortcut that you don't want, or if you accidentally create a shortcut; For example, if you have a link between two fields with different data types, you can delete the link.

  1. In the query design grid, click the link that you want to remove.

  2. Press CLEAR.

- or -

  • In the query design grid, right-click the shortcut that you want to remove, and then click Clear.

Top of page

See also

Adding

Combine

Create

Create,