Query to Show Available Employee (1 Viewer)

Hi, I wish to create query where I can select the available employee for the period of job(engagement). For every engagement will form a team of multiple employees. I will select the employee and assign them into a team according to team ID.

The tables related are as below:

tblEngagement: EngagementID, ClientID, ClientName, EngagementType, StartDate & EndDate

tblEmployee: EmployeeID, EmployeeName, JobTitle

tblTeam: TeamID, EngagementID, TeamName

tblAllocation:TeamID fk, EmployeeIDfk - This table is the intermediate table between Team and Employee as both tables are having many to many relationship.

Can anyone briefly teach me how to do it?

You might consider this, and I'll explain it as I go.

Build a query that JOINs tblEmployee with tblAllocation. Normally you would do an INNER JOIN to show all allocated employees - but this time when make the query, build a temporary relation in the design area such that you have all employees and any matching allocations. This is an OUTER JOIN for which you will get a list of all employees and, for any that have a TeamID, you can see it.

Now all it takes to find an unallocated employee is to find one in that join query for which the TeamID is null - because that OUTER JOIN will supply nulls for fields where no match existed. In other words, you are looking for someone who DOESN'T have a TeamID at the moment. And because of the OUTER JOIN, that will show up as a TeamID that is a NULL. Note that you test with ISNULL(fieldname) because you cannot use field = NULL. The latter syntax does not like NULLs because no matter what value you have, a NULL doesn't equal it - including another NULL, actually.

create this Query:

SELECT tblEmployee.EmployeeID, tblEmployee.EmployeeName, tblEmployee.JobTitle, tblAllocation.TeamID, tblEngagement.EngagementID, tblEngagement.EndDate
FROM ((tblEmployee LEFT JOIN tblAllocation ON tblEmployee.EmployeeID = tblAllocation.EmployeeID) LEFT JOIN tblTeam ON tblAllocation.TeamID = tblTeam.TeamID) LEFT JOIN tblEngagement ON tblTeam.EngagementID = tblEngagement.EngagementID
WHERE (((tblAllocation.TeamID) Is Null)) OR (((Nz([EndDate],Date()-1))<Date()));

Click to expand...

this will show employee that has not included in any team or already in a team but the team's last engagement event has already been past.

You've got an issue with your relationships in that screen shot. There should only be one way to travel between tables in a relationship and you have a circular path. You can travel from Task to Employee directly, or you can travel between those two via STaffAllocation. That is incorrect.

What you should do is add a primary key autonumber to STaffAllocation (StaffAllocationID). Remove TeamID and AssignedTo from Tasks, add a new field (StaffAllocationFk) to Tasks and store the value of the new primary key in STaffAllocation (StaffAllocationID) in it.