MS Access connection

A database is a collection of data tables that allow logical relationships with each other. Y ou can use relationships to connect tables through fields that they have in common. A table can be part of any number of relationships, but each relationship always has two identical tables. A t the time of the query, the relationship is represented by Jon.

What is a connection

Connections specify how records for two or more tables in the database are combined. C onceptually, connections are very similar to table relationships. In fact, a connection is the relationship between a query and a table.

Here are two basic types of connections that we'll discuss in this chapter:

• internal connection
• external connection

Both can be easily created from the query design view.

The connection within

Now let's look at the connection within -

• The most common type of connection is an internal connection, which is also the default connection type in Microsoft Access.
• The inner connection displays only rows or records that are equal to the join fields in the two tables.
• This connection type views these common fields and the data they contain. It only shows matches.

external connection

Now let's understand what an external connection is -

• An outer connection displays all rows in one table, and only rows or records in another table with equal connection fields.
• In other words, the outer connection displays all the rows in one table, but only the corresponding rows in the other table.

There are other types of connections -

The outer left connection and the right outer connection

Now let's look at the left outer connection and the right outer connection -

• You can choose a table that displays all rows.
• You can create a left-field connection that contains all the rows in the first table.

• You can create a right outer connection that contains all the rows in the second table.

Now let's go to the Create tab and create a query from the design view. Select tblProjects and tblTasks, and close the Display Table dialog box, as shown in the screenshot below.

Add the ProjectName field from tblProjects and TaskTitle, StartDate, andDueDate from the tblTasks table.

Now let's run the query.

We only show records for a few items. Several of these projects have many tasks related to the project, and this information is related through the ProjectID field.

• When we create this query in Microsoft Access, Access gets this relationship from the relationship we create.

• By default, it creates an internal connection between the two tables, which is how this information is associated with the two tables. /b10>。

• It only shows us a match, so when we run this query, many of the other items listed in tblProjects don't appear as part of the records we set up in this query, because of how the two tables are connected together by an internal connection (which is the default for any Of query).

However, if you want to change the relationship, let's say you want to create an external connection, or in other words, display all items from tblProjects, each record in the table, and all tasks from tblTasks - open connection properties; W e can simply do this by double-clicking the relationship line.

Access displays the left table name and the right table name in the Join Properties dialog box.

• The left and right column names, as well as the first single button, contain only rows in two tables where the connection fields are equal, that is, internal connections, and this is selected by default when creating a relationship, when a connection is created in a query, but you can change it.

• We have two other options; We can include all records from tblProjects, and only those from tblTasks, where the connection fields are equal, and this one is a left-field connection.

• We have a third option, which includes all records from tblTasks, including only those from tblProjects, where the connected fields are equal and this one is a right-outside connection.

These are the different types of connections that you can easily create from the design view. Let's select the second option, the left outer connection, and then click Ok.

Now let's look at the other steps -

When you look at the relationship line, you'll see a small arrow pointing to the ProjectID in tblTasks. When you run this query, you will see the following results.

As you can see, it shows each of our project names, whether it has related tasks or not. ne. ll of this will be blank because there is no relevant information in tblTasks, and these fields come from. N ow let's go to the design view again and double-click the relationship line.

In the Add Properties dialog box, select the third option for the right-outside connection, and then click OK.

Now look at our relationship line. Y ou'll see a small arrow now pointing to the ProjectID in tblProjects. When you run this query, you will see the following results.

Self-join

A self-join is another type of join. S elf-connection involves matching fields from within the same table. F or example, look at an employee table that has a supervisor field that references the same type of number stored in another field in the same table, the employee ID.

If we want to know who the supervisor of Kaitlin Rasmussen is, we will have to get the number stored in the supervisor field and look for it in the exact same table in the employee ID field to know that Charity Hendricks is the supervisor.

This table is not the ideal structure for a relationship database because it is not normalized.

If we have a situation where we want to create a query that lists only the supervisor name next to the employee name, there is no simple way we can query unless we create a self-connection.

To view the self-connection, create a table with the following fields and enter some data.

Here, we're going to create a list again that contains the employee's name, followed by the supervisor's name. Let's create a query from the query design view.

Now, add the tblEmployees table.

Close this dialog box.

Now add first and last names to our employees.

We now need a way to create a relationship between this table and itself. To do this, we need to open the Display Table dialog box and add tblEmployees again.

We created another copy of the same table in this query view. N ow we need to create a self-connection. le. hen, add the first and last names from the copied table.

Now let's run the query and you'll see the following results.

It displays the name of the supervisor next to the employee's name. Also, this is how to create a self-connection in Microsoft Access.