I can't figure out how to do this.

  • Thread starter Thread starter SF
  • Start date Start date
S

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
 
This query should work:

SELECT EmployeeId
, TravelDateFrom
, TravelDateTo
, "In OFFICE" AS Status
FROM TravelTable
WHERE
TravelDateFrom Is Not Null AND
TravelDateTo Is Not Null AND
Date() Not Between [TravelDateFrom] And [TravelDateTo];
 
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 :)
 
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
 
Thank you for your tip. I will try that.

SF


John Spencer said:
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
Click to expand...
 
Back
Top