Access - Best way to return employee list based on another table

Question

Hopefully I can explain this in a way that someone will understand what I am trying to achieve and be able to offer a possible solution.

I am creating a database for managing Health and Safety at my workplace. I have one table created to store employees - "Table: Employees". This table has the usual fields (First Name, Last Name etc.) but specifically has the following fields: "Departments" (Multiple selection as employees can move between), "Additional Competencies" (e.g. Forklift, Order Picker etc. (Again multiple selection)), and "Gender".

I have a second table ("Table: Toolbox Talks") which is where the system stores tool box talks (created by the user through a form. "Form: Toolbox Talk"). The intended recipients of a toolbox talk will generally be limited to either one or more departments, or maybe just all forklift drivers or all of one gender. When the user creates a new toolbox talk (record), they will in the form (Form: Toolbox Talk) select from drop down lists the appropriate variables (Department, Competency, Gender). (N.B. a toolbox talk can sometimes be relevant to more than just one.)

What I am trying to achieve is to some how create a query that for each toolbox talk, it will return only the employees who are required to participate in the toolbox talk.

For example: Say a toolbox talk is required to be carried out for all "female" employees who work in "warehousing". I would then want my database in a datasheet subform on form - "Form: Toolbox Talk" to return all employees who are female and are marked as able to work in warehousing department.

I'll just add that I would like to avoid the intended system user using the filter buttons on the ribbon and my preference would be to use query's or vba if possible.

What I have tried so far:

1. I created a query ("Query: EmployeesperToolboxTalk") and added two tables ("Query: EmployeesExtended" & "Query: ToolboxTalksExtended"). I tried creating joins between the relevant fields (e.g. Gender to Gender, Department to Department etc.) but this did not work as either join properties 1, 2, or 3. If I limit my join to say just departments, then that seems to do what I want but it doesn't include the other two variables which isn't really how I want it to work.

SELECT [Query: EmployeesExtended].[Employee Name], [Query: EmployeesExtended].Department, [Query: ToolboxTalksExtended].[Toolbox Talk], [Query: ToolboxTalksExtended].ToolTalkID, [Query: ToolboxTalksExtended].RecipientVariables
FROM [Query: EmployeesExtended] INNER JOIN [Query: ToolboxTalksExtended] ON ([Query: EmployeesExtended].Gender = [Query: ToolboxTalksExtended].RelatedGender) AND ([Query: EmployeesExtended].AdditionalCompetency.Value = [Query: ToolboxTalksExtended].RelatedCompetency.Value) AND ([Query: EmployeesExtended].Department.Value = [Query: ToolboxTalksExtended].RelatedDepartment.Value);

2. Following that, I created a union query to combine all the different variables into one:

SELECT [Table: Competencies].Competency
FROM [Table: Competencies];
UNION 
SELECT [Table: Departments].Department
FROM [Table: Departments];
UNION SELECT [Table: Gender].Gender
FROM [Table: Gender];

I then added a field "RecipientVariables" to table: "Table: Toolbox Talks" and set the row source to:

SELECT [Query: RecipientVariables].Competency
FROM [Query: RecipientVariables];

I then created a join from "RecipientVariables" to Departments, AdditionalCompetency and Gender:

SELECT [Query: EmployeesExtended].[Employee Name], [Query: EmployeesExtended].Department, [Query: ToolboxTalksExtended].[Toolbox Talk], [Query: ToolboxTalksExtended].ToolTalkID, [Query: ToolboxTalksExtended].RecipientVariables
FROM [Query: ToolboxTalksExtended] INNER JOIN [Query: EmployeesExtended] ON ([Query: ToolboxTalksExtended].RecipientVariables.Value = [Query: EmployeesExtended].Department.Value) AND ([Query: ToolboxTalksExtended].RecipientVariables.Value = [Query: EmployeesExtended].AdditionalCompetency.Value) AND ([Query: ToolboxTalksExtended].RecipientVariables.Value = [Query: EmployeesExtended].Gender);

This returns no results using join properties 1, 2 or 3.

3. The last thing I have tried is to create a second union query from my employees table ("Table: Employees"):

SELECT [Table: Employees].AdditionalCompetency.Value
FROM [Table: Employees];
UNION
SELECT [Table: Employees].Gender
FROM [Table: Employees];
UNION SELECT [Table: Employees].Department.Value
FROM [Table: Employees];

I then added a new field into my employee table: "AreasofCompetency" and set the Row Source as: "Query: EmployeeVariables"

This also doesn't work.

I am now at a loss of what to try next. Perhaps what I want to do is not possible or its much easier than I realise.

If someone can please give some guidance or perhaps offer an alternative suggestion, I would be very grateful. Please let me know if any additional info is required and I will gladly provide it.

Many thanks in advance.

Answer 1

as per my comment first normalize your table structure. For instance something like:

Then create a query that selects everything related to employees. Here that is everything. Select that query and click create form and access will make a form for you: The unbound combobox and unbound listbox in the header are actually not there yet and the form shows every variable in the record source and hence is not user friendly. add comboboxes and listboxes to the header for every variable you want to filter employees by. Also delete and or move the labels and textboxes as you want. Here I got:

Then you want to edit the after update event for the various unbound controls to filter the records. for instance see Filter form based on two combo box selections

Private Sub cmbCompetency_AfterUpdate()
'cheap example hookup, same code for lstDepartments
Me.Filter = "EmployeesCompetencies.CompetencyID = " & cmbCompetency & " AND EmployeesDepartments.DepartmentID = " & cmbDepartment
Me.FilterOn = True
End Sub

this gets you: