Using Junction Tables

[Guest]

Hello,

How and Where do you use Junction tables?

I'm trying to create a many-to-many relationship between Employees and
Modules. I have 4 tables related like such:

Employees EmpCourseJoin Modules MainTable
EmpID EmpID ModID MainID ( PK )
EmpName ModID DocNum EmpID ( FK to Employees )
ModID ( FK to
Modules )

Q. How and where do I use the EmpCourseJoin table.

Q. What exactly is the Join table supposed to do.

-Simon

 

[Graham Mandeno]

Hi Simon

A relationship between two tables must have a unique key on one side.
Therefore, the only relationships that can be directly represented are those
which have a "one" side, i.e. one-to-one, and one-to-many relationships.

Many relationships in real life are many-to-many. For example, one employee
can enrol in many courses, and one course can accommodate many employees.
These many-to-many relationships must be modeled using a junction table and
two one-to-many relationships.

The junction table, therefore, contains TWO foreign key fields, which
represent both the "many" sides of the two relationships.

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

So, a junction table represents a many-to-many relationship. It is
generally used to simulate a one-to-many relationship between one of the
tables, and a *query* based on the junction table and the other table.

For example, you could create a form showing employee details with a subform
(based on a query) showing details of the courses that employee has enrolled
in.

Similarly, you could create a form showing course details with a subform
(based on a query) showing details of the employees that enrolled in that
course.

Hope this helps

 

[Guest]

Graham,

Dude, you answered my questions so well that it has changed my whole
approach when dealing with tables and queries. You've helped me realize that
I was going about things in the wrong way. A lot of confusion I once had
about the relational side of Access has become a lot easier to understand now.

I still have not grasped the full scope and the benefits linking tables, but
I'm many steps further to it than I was before. Thanks Graham for all the
details ( meat with potatoes ), your awesome dude.

-Simon

How can I populate a subform

Hi Simon

A relationship between two tables must have a unique key on one side.
Therefore, the only relationships that can be directly represented are those
which have a "one" side, i.e. one-to-one, and one-to-many relationships.

Many relationships in real life are many-to-many. For example, one employee
can enrol in many courses, and one course can accommodate many employees.
These many-to-many relationships must be modeled using a junction table and
two one-to-many relationships.

The junction table, therefore, contains TWO foreign key fields, which
represent both the "many" sides of the two relationships.

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

So, a junction table represents a many-to-many relationship. It is
generally used to simulate a one-to-many relationship between one of the
tables, and a *query* based on the junction table and the other table.

For example, you could create a form showing employee details with a subform
(based on a query) showing details of the courses that employee has enrolled
in.

Similarly, you could create a form showing course details with a subform
(based on a query) showing details of the employees that enrolled in that
course.

Hope this helps
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,

How and Where do you use Junction tables?

I'm trying to create a many-to-many relationship between Employees and
Modules. I have 4 tables related like such:

Employees EmpCourseJoin Modules MainTable
EmpID EmpID ModID MainID ( PK )
EmpName ModID DocNum EmpID ( FK to Employees )
ModID ( FK to
Modules )

Q. How and where do I use the EmpCourseJoin table.

Q. What exactly is the Join table supposed to do.

-Simon

 

[Guest]

The Form part is not working for me.
i am not able to know the correct query design and which fields to choose
and which not to.
Also, how the main form and the subform link to one another.

Hi Simon

A relationship between two tables must have a unique key on one side.
Therefore, the only relationships that can be directly represented are those
which have a "one" side, i.e. one-to-one, and one-to-many relationships.

Many relationships in real life are many-to-many. For example, one employee
can enrol in many courses, and one course can accommodate many employees.
These many-to-many relationships must be modeled using a junction table and
two one-to-many relationships.

The junction table, therefore, contains TWO foreign key fields, which
represent both the "many" sides of the two relationships.

Often the junction table contains only those two fields, but sometimes it
may be appropriate to store additional data there. This must happen ONLY if
the data are attributes of the COMBINATION of the two foreign keys. For
example, the date enrolled, date completed, and final result are all
attributes of the combination of employee and course.

So, a junction table represents a many-to-many relationship. It is
generally used to simulate a one-to-many relationship between one of the
tables, and a *query* based on the junction table and the other table.

For example, you could create a form showing employee details with a subform
(based on a query) showing details of the courses that employee has enrolled
in.

Similarly, you could create a form showing course details with a subform
(based on a query) showing details of the employees that enrolled in that
course.

Hope this helps
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello,

How and Where do you use Junction tables?

I'm trying to create a many-to-many relationship between Employees and
Modules. I have 4 tables related like such:

Employees EmpCourseJoin Modules MainTable
EmpID EmpID ModID MainID ( PK )
EmpName ModID DocNum EmpID ( FK to Employees )
ModID ( FK to
Modules )

Q. How and where do I use the EmpCourseJoin table.

Q. What exactly is the Join table supposed to do.

-Simon

 

[DavidRBoyle]

The creation of junction tables often means that the user interface needs to allow movement of a list of items from being included or not included from the corresponding group. As you've found using bound forms to do this can be a nightmatre and you need an unbound form to manage the many to many relationship.

I use the following approach to do this:

For illustrative purposes assume you have a set of categories in an employee time analysis application. Each Category can have many sub categories, but the sub categories can also belong to more than one category.

On a blank form

1) Create a combo box style drop down list that selects the category list. Include the category identifier as a hidden field. The combo box wizard will let you do this.

2) Add two unbound list boxes - set their Multi Select property to be "extended". Call the list boxes something useful like lstMatched and lstUnMatched because you will refer to them in code.

Create two queries, one which represents the list of categories included in the category like this qryMatchedInCategory -

SELECT tblCatSubCat.id, tblSubCategory.Description, tblCatSubCat.idSubCat
FROM tblSubCategory LEFT JOIN tblCatSubCat ON tblSubCategory.id = tblCatSubCat.idSubCat
WHERE (((tblCatSubCat.idCat)=[Forms]![FrmCategoryMaintenance]![cboCategory]))
ORDER BY tblSubCategory.Description;

and one which represents the unmatched sub categories
- use the query described above in the unmatched query wizard.

SELECT tblSubCategory.id, tblSubCategory.Description
FROM tblSubCategory LEFT JOIN qryMatchedInCategory ON tblSubCategory.id = qryMatchedInCategory.idSubCat
WHERE (((qryMatchedInCategory.idSubCat) Is Null))
ORDER BY tblSubCategory.Description;

Use these two queries as the row source of lstMatched and lstUnMatched respectively.

Set the list box column counts and column widths to 2 and something like "0cm;2cm" so the left column isn't shown

Add two buttons cmdMoveToLeft with a "<" symbol on it. cmdMoveToRight with a ">" symbol on it. You use to move records from matched to unmatched lists. You can adapt the code to have "<<" ">>" move all buttons if that seems appropriate.

Use code like that shown below

Option Compare Database
Option Explicit

Dim strSQL As String

Private Sub cboCategory_AfterUpdate()
Call UpdateListBoxes
End Sub

Private Sub cmdMoveToLeft_Click()
Dim lngRow As Long
DoCmd.SetWarnings False
With Me.lstMatched
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strSQL = "DELETE * FROM tblCatSubCat WHERE id=" & .Column(0, lngRow) & ";"
DoCmd.RunSQL strSQL
End If
Next lngRow
End With
Call UpdateListBoxes
End Sub

Private Sub cmdMoveToRight_Click()
Dim lngRow As Long
DoCmd.SetWarnings False
With Me.lstUnMatched
For lngRow = 0 To .ListCount - 1
If .Selected(lngRow) Then
strSQL = "INSERT INTO tblCatSubCat(idSubCat,idCat) VALUES (" & .Column(0, lngRow) & "," & Me.cboCategory & ");"
DoCmd.RunSQL strSQL
' .Column(0, lngRow) is id
End If
Next lngRow
End With
Call UpdateListBoxes
End Sub

Private Sub UpdateListBoxes()
Me.lstMatched.Requery
Me.lstUnMatched.Requery
End Sub