I can't figure out how to do this.

[SF]

Hi,

I can't figure out how to do this. I want to check if staff is available in
the office by create query from TravelTable (TravelID, EmployeeID,
TravelDateFrom, TravelDateTo).
The query should list anyone who are not travelling today. Would it be
possible or not?

SF

 

[Krzysztof Naworyta]

SF wrote:

| I can't figure out how to do this. I want to check if staff is
| available in the office by create query from TravelTable (TravelID,
| EmployeeID, TravelDateFrom, TravelDateTo).
| The query should list anyone who are not travelling today. Would it be
| possible or not?

It is possible

 

[John Spencer]

Assuming you have an Employee table, this is possible

The basic idea is to create a query that shows everyone that IS traveling on
the date specified and then use that query to get everyone that is not traveling.

This query should get everyone that is traveling today
SELECT EmployeeID
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo

So, the following should work to give you employees not traveling.

SELECT *
FROM EmployeeTable
WHERE EmployeeID NOT IN
(SELECT EmployeeID
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo)

You can also use
SELECT *
FROM EmployeeTable
WHERE EmployeeID NOT EXISTS
(SELECT *
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo
AND TravelTable.EmployeeID = EmployeeTable.EmployeeID)

Or other variations are available.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[SF]

Thank you for your tip. I will try that.

SF

Assuming you have an Employee table, this is possible

The basic idea is to create a query that shows everyone that IS traveling
on the date specified and then use that query to get everyone that is not
traveling.

This query should get everyone that is traveling today
SELECT EmployeeID
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo

So, the following should work to give you employees not traveling.

SELECT *
FROM EmployeeTable
WHERE EmployeeID NOT IN
(SELECT EmployeeID
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo)

You can also use
SELECT *
FROM EmployeeTable
WHERE EmployeeID NOT EXISTS
(SELECT *
FROM TravelTable
WHERE Date() Between TravelDateFrom and TravelDateTo
AND TravelTable.EmployeeID = EmployeeTable.EmployeeID)

Or other variations are available.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County