How to combine the two?... (1 Viewer)

[Matizo]

Hi,

Basically, I've got to separate queries. 1st that shows services in a given time frame (defined by user) and 2nd that shows not allocated employees (using "Is Null").

What I need now is how to combine the two queries taking into account the fact that employee might be allocated to a job this week, but available the next week (which the 2nd query does not show).

I want user to define time frame and the query to show employees which are not allocated in this time frame.

Cheers,

 

[jdraw]

Hi,

Basically, I've got to separate queries. 1st that shows services in a given time frame (defined by user) and 2nd that shows not allocated employees (using "Is Null").

What I need now is how to combine the two queries taking into account the fact that employee might be allocated to a job this week, but available the next week (which the 2nd query does not show).

I want user to define time frame and the query to show employees which are not allocated in this time frame.

Cheers,

Can you show the code for the queries?

 

[Matizo]

Hi,

Thanks for reply,

The code of query that shows unallocated employees is as follows:

SELECT tblEmployees.EmployeeNo, tblEmployees.Forename, tblEmployees.Surname, tblAllocations.ServiceNo
FROM tblEmployees LEFT JOIN tblAllocations ON tblEmployees.EmployeeNo = tblAllocations.EmployeeNo
WHERE (((tblAllocations.ServiceNo) Is Null));

The code of query that shows the time frame is:

SELECT tblAllocations.EmployeeNo, tblServices.ServiceNo, tblServices.ServiceTitle, tblServices.StartDate, tblServices.EstimatedDuration, [StartDate]+[EstimatedDuration] AS EndTime
FROM tblServices INNER JOIN tblAllocations ON tblServices.ServiceNo = tblAllocations.ServiceNo
WHERE (((tblServices.StartDate)>=[Enter Start Date:]) AND (([StartDate]+[EstimatedDuration])<=[Enter End Date:]));

What's more, I'm not 100% confident that this is the right way to do it, but it's the only idea I have.

Thanks!