Assign Employees to task then remove employee from list of available for other tasks

[chrisargo]

I need the ability to assign employees to a different task in each of four different timeslots on a daily basis. What I would like to do is as I assign an employee to a task in timeframe 1, I would like for them to become unavailable to assign to another task in the same timeframe.

For Example: Three employees to assign to three tasks at the 8 AM to 10 AM time slot.

Employee	Task	Time	Assigned
Emp 1, Emp 2, Emp 3	Task 1	8 AM to 10 AM	Emp 2—Task 1
Emp 1, Emp 3	Task 2	8 AM to 10 AM	Emp 3—Task 2
Emp1	Task 3	8 AM to 10 AM	Emp1—Task 3

It would be nice to set this up in a form where my person doing the scheduling can assign an individual to a task and that individual name disappears from the combo boxes, list boxes, or is “greyed-out” in a listing of employees available for the time period for the remaining assignments.

Any thoughts? I’ve used Access off and on for about 12 years to create various databases for internal use. It has been a while since I’ve had to do some work, so any advice would be greatly appreciated.

[June7]

This is a common topic in forum.

Only way I know requires a dataset of all combinations of employees and tasks and timeframe. This can be created by a query that includes tables without a JOIN clause, resulting in a Cartesian relation of records. Then join this dataset to the table that stores EmployeID and TaskID and timeframeID by compound link (join type "all records from {the Cartesian dataset}" and apply filter Is Null criteria to the fields from task assignment table.

However, you will probably find this will not work nicely in a continuous or datasheet form if the combobox RowSource includes lookup with alias - meaning the EmployeeID is saved but Employee Name is displayed. The restricted combobox RowSource will not have names available for employees not listed and therefore those records will appear empty for that field.

[ItsMe]

What have you tried so far?

[chrisargo]

I just started working on this idea this morning. I have tried various permutations of queries and queries upon queries. My idea was to create a query that lists employees who have not been assigned a task in the task/timeframe. After assigning a task to an employee, the query would need to re-run in order to reflect the recent entry. However, I can't seem to create a query that will display employees who have not been assigned a task in the specific timeframe. In other words, my simplified assignment table is similar to:

Date	Task	Timeframe	Employee
05/24/2015	Task 1	8 AM to 10 AM	Employee 2
05/24/2015	Task 2	8 AM to 10 AM
05/24/2015	Task 3	8 AM to 10 AM

The issue I run into is that my select queries will only select employees from the master employee table who are already assigned a task in the task table. What I need is the inverse. I need to select employees from the employee table who are not assigned a task in the task table. If I can figure that out, I may be able to make a workable solution. Can anyone tell me if what I am trying to do in selecting the inverse is even possible?

[June7]

Did you see post 2?

[chrisargo]

I did. Much thanks for your instructions. Initially, it makes sense to me. I'm processing through the steps to see how well it will fit my needs as well as taking to heart your point about it possibly not working smoothly with the way I have my lookups set up.

Thanks again.

[ItsMe]

...Can anyone tell me if what I am trying to do in selecting the inverse is even possible?...

You may need to rethink your approach.

However, if you are onto something with your current approach and table designs, you might try the Query Wizard and select Find Unmatched Records.

By joining fields, typically fields that are not PK's, you add Is Null criteria to one of the sides.

Here is a sample SQL. Keep in mind the Is Null will be the record you will retrieve. You want to retrieve the record that does not have a match, the record that is null when considering the JOIN.

SELECT aqryModified.Web_Address
FROM aqryModified LEFT JOIN TableB ON aqryModified.aDomainDot = TableB.DomainDot
WHERE (((TableB.DomainDot) Is Null));

[chrisargo]

Much thanks. I will give this some thought and testing this afternoon/evening. I appreciate all the help.