Relationships in Access:

Currently I have a pay table that contains a department number and a department table that contains a department number and a department name. I want to establish the relationship between these two tables. The employee table has the employee id as the primary key and the department table has the department number as the primary key.

To create the relationship, go to the Tools menu and select Relationships. You will get a blank Relationship window with the Show Table dialog box open inside. (If by chance you do not see the Show Table, go to the Relationship menu and select it). Add the two tables that you are using (pay and dept).

Now you want to make the link between the two. Click on the empdept in the pay table and drag it over to the empdept in the dept table. The Relationship dialog box will now appear. At this point it is recommended that you click on Enforce Referential Integrity. If the relationship was established wrong, you can make modifications before you click on create. Notice that a One-To-Many relationship has been established. Once you click on create, the relationship will be established as shown in the second picture on the next page.

You can now create a query with both tables. When the design asks you to select tables, add both of them. Then you can pick fields and establish the query information as shown. Notice that because you have established a relationship, the relationship between the two tables shows in the query setup. Notice also that when you select fields from the tables, the field name and the table name are shown in the query. These two tables are joined. If you look at the SQL, it will show the code as an INNER JOIN. An inner join is a join combines records from two tables where there is a matching value in the field common to both of the tables. To quote Miscrosoft Access Help:

"You can use an INNER JOIN operation in any FROM clause. This is the most common type of join. Inner joins combine records from two tables whenever there are matching values in a field common to both tables.

You can use INNER JOIN with the Departments and Employees tables to select all the employees in each department. In contrast, to select all departments (even if some have no employees assigned to them) or all employees (even if some aren't assigned to a department), you can use a LEFT JOIN or RIGHT JOIN operation to create an outer join. Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table."

SQL:

SELECT pay.empid, pay.emplname, pay.empdept, dept.deptname

FROM dept INNER JOIN pay ON dept.empdept = pay.empdept;

Output:

empid

emplname

empdept

deptname

12345

Ash

12

IS

78901

Smith

12

IS

90123

Brown

12

IS

23456

Costa

15

Sales

45678

Trent

15

Sales

56789

Brooks

15

Sales

92345

Hersey

15

Sales

94567

Doe

15

Sales

34567

Farmer

17

Payroll

67890

Souza

17

Payroll

89012

Ames

17

Payroll

91234

Daniels

17

Payroll