Using Junction Tables

S

Simon

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
 
G

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 :)
 
S

Simon

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

Graham Mandeno said:
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

Simon said:
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
Click to expand...
Click to expand...
 
A

ALM

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.



Graham Mandeno said:
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

Simon said:
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
Click to expand...
Click to expand...
 
Top