Query to Show Available Employee

[eugenelim0410]

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?

 

[The_Doc_Man]

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.

 

[arnelgp]

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()));

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.

 

[plog]

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.