Help with Junction Table Join

[jeffcampbell]

Hi Everyone,
I'm working on my first "real" Access database, intended to keep track of employee who receive recognition for performance. It quickly got more complex when it turned out that it required a "many to many" relationship, i.e. employee can recieve multiple awards, and an individual award can be shared by multiple employees. I puzzled out (with some previous help offered here) that I needed a junction table. So, right now, I have tblEmployee, tblAward, and tblJunctionEmployeeAward. I've build a form with a combo box and a join that allows me to page through employees and see associated awards, add a new employee, and by using "NotOnList" and a little bit of code, add a new award. Here's where I need help. In tblAward, I currently have three fields (Department, AwardType, ToPersonnelFile) which can be different for different employees. For example, Employee#1 could recieve a gift card, and have nothing sent to their personnel file and Employee#2 could get a letter of appreciation, and have documentation of the award sent to their personnel file. So, I added these two field to tblJunctionEmployeeAward, and tried to do a Join in the Form properties. Well, when I try the Join, when I open the Form, I'm prompted to enter the Department. If I enter a valid Department number, the from opens up. I'm assuming at this point that I either need to create more Junction Tables, or I'm doing the Join incorrectly. Any help would be appreciated!!!
Thanks!

 

[jzwp22]

If the way the award is handled (to personnel file, letter of appreciation, gift card) is different for each employee receiving the award, then you cannot have the field ToPersonnelFile in the award table. For simplicity, I will call the way the award is handled as award actions. With that said, would it go in the junction table? It depends... You said the following:

Employee#2 could get a letter of appreciation, and have documentation of the award sent to their personnel file

This statement implies that 2 actions can be taken with respect to an employee's reward. If more than one action, then we have a one(employee/award)-to-many (actions) relationship which needs another table


I assume your junction table looks something like this:

tblEmployeeAwards
-pkEmpAwardID primary key, autonumber
-fkEmployeeID foreign key to tblEmployees
-fkAwardID foreign key to tblAwards

Since various types of actions can happen with an employee's award, I would set up a table to hold the various actions (to personnel file, letter of appreciation, gift card)

tblAwardActions
-pkAwardActionID primary key, autonumber
-txtAwardAction

Now we need to assign the various actions to the employee getting the award

tblEmployeeAwardActions
-pkEmpAwardActionID primary key, autonumber
-fkEmpAwardID foreign key to the junction table tblEmployeeAwards
-fkAwardActionID foreign key to tblAwardActions

As to the department, I do not think that the department field should even be in the award table. The employee belongs to a department, but not necessarily the award, I assume. You'll have to provide more info on that aspect. And that info will help determine the best place for the department field. If you only want to record the current department of an employee, then the department field belongs in the employee table. However, if you are also tracking the movement of employees from one department to another (one(employee)-to-many(departments) over time, then you need a new separate table to handle that. Additionally, if you have a table that holds the departments, then that new table will need to be a junction table. The only condition I see where the deparment field is in the award table is if each award is specific to only 1 department.

 

[jeffcampbell]

Hi jzwp22,
Thank you very much for helping me. Here’s some more info. My Junction Table, tblJunctionEmployeeAward has two fields, EmployeeID and AwardID. EmployeeID is a foreign key to tblEmployee and AwardID is a foreign key to tblAward. (both EmployeeID and AwardID are set to Primary Key, Indexed with Duplicates Allowed. Both are set as ComboBoxes with the Row Source Type set as Table/Query and Row Source set to tblEmployee and tblAward, respectively) Here the business logic behind the database: an employee can receive one of 5 different types of recognition for an award (values held in AwardType) and regardless of the type of recognition they receive for the award, documentation can be sent to their personnel file (Yes/No value held in ToPersonnelFile). With regard to Department, I have a Department field in tblEmployee, which carries the value of the department to which the employee is currently assigned. I have a Department field in tblAward which is department to which the employee was assigned when they received the award.

So, if I’m following your post correctly, I will need to pull Department, AwardType, ToPersonnelFile from tblAward create three new tables: tblAwardDepartment, tblAwardActions, and tblAwardToPersonnelFile, as well as three new junction tables, tblJunctionAwardDepartment, tblJunctionAwardAction, and tblJunctionAwardToPersonnelFile. I still need a little help about how to do the Join in the Form, specifically how to created the “nested” the Inner Joins. Here’s my Record Source for my Form:

SELECT tblAward.AwardID, tblAward.Department, tblAward.Department, tblAward.AwardType, tblAward.ToPersonnelFile, tblJunctionEmployeeAward.EmployeeID, tblJunctionEmployeeAward.AwardID FROM tblAward INNER JOIN tblJunctionEmployeeAward ON tblAward.AwardID=tbl tblJunctionEmployeeAward. AwardID;

Thanks!!

 

[jzwp22]

both EmployeeID and AwardID are set to Primary Key, Indexed with Duplicates Allowed. Both are set as ComboBoxes with the Row Source Type set as Table/Query and Row Source set to tblEmployee and tblAward, respectively)

I generally have a unique field as the primary key rather than a composite key. Additionally, although Access has the capability of having lookups (combo & list boxes) at the table level, it is generally not recommended. You might want to take a look at this site for more details.

With regard to Department, I have a Department field in tblEmployee, which carries the value of the department to which the employee is currently assigned. I have a Department field in tblAward which is department to which the employee was assigned when they received the award.

If you want to record the department to which the employee belonged at the time of the award then it must go in your employee-award junction table. The award table should only describe the award itself. Since your employee table may have a different current department compared to that in the junction table, you should not make a join between these two department fields

I will need to pull Department, AwardType, ToPersonnelFile from tblAward create three new tables: tblAwardDepartment, tblAwardActions, and tblAwardToPersonnelFile, as well as three new junction tables, tblJunctionAwardDepartment, tblJunctionAwardAction, and tblJunctionAwardToPersonnelFile.

Not quite...


The department should just be a field in your existing junction table as described above (so you will not need tblJunctionAwardDepartment or tblAwardDepartment)

ToPersonnelFile would just be another action, it should be a record (not a field) in tblAwardActions along with letter of appreciation and gift card. There is no need for tblJunctionAwardToPersonnelFile.


You will need an junction table similar to this:

tblEmployeeAwardActions
-pkEmpAwardActionID primary key, autonumber
-fkEmpAwardID foreign key to the junction table tblEmployeeAwards
-fkAwardActionID foreign key to tblAwardActions

As you can see, I use the fkEmpAwardID to join back to your original junction table. I am not sure how you would make the join using a composite key in your original junction table (that's why I don't use composite keys).

As to your form, you will need 1 main form with a subform and within that another subform (or subsubform). Each form/subform will be based on an individual table not a query.

If I were setting this up, I would set up the main form bound to the employee table with a subform bound to the junction table (that subform will have a combo boxes to populate the award and the department at the time of the award. This subform will have a subform based on the tblEmployeeAwardActions (probably a form in datasheet format). In the subsubform would be a combo box to populate the action.