If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
I can't figure out how to do this.
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 |
#2
|
|||
|
|||
I can't figure out how to do this.
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 -- KN |
#3
|
|||
|
|||
I can't figure out how to do this.
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 wrote: 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 |
#4
|
|||
|
|||
I can't figure out how to do this.
Thank you for your tip. I will try that.
SF "John Spencer" wrote in message ... 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|