Help with displaying list.

[Guest]

I am trying to display a list of employees that do not have vacation on a
certain date. The SQL is below. It kinda of works. The problem is it only
shows the employees that have vacation scheduled, and if they have more than
one date they will be listed multiple times. If you are an employee that
does not have any vacation scheduled your name is not displayed. How do I
show only one employee name and all employees that do not have vacation on
that date?

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name,
tblVacation.VacationDate
FROM tblEmployee AS E LEFT JOIN tblVacation ON E.EmployeeID =
tblVacation.EmployeeID
WHERE (((tblVacation.VacationDate)<>[forms].[frmschedule].[txtrecorddate]))
ORDER BY E.FirstName, E.LastName;

 

[Guest]

Try this

SELECT tblEmployee.EmployeeID, tblEmployee.FirstName & " " & [LastName] AS
Name,
FROM tblEmployee
tblEmployee.EmployeeID Not in (Select tblVacation.EmployeeID From tblVacation
WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtrecorddate] )
ORDER BY tblEmployee.FirstName, tblEmployee.LastName

 

[Guest]

Thank you for your help.

That part works and now I need to add one more thing. I would like
cboEmployeeID to only display the employees that are allowed to do that
posistion from cboPosition on the same form. The positions allowed are
stored in a table that uses the employeeID and positionID. An employee can
have more than one position id in the table. Any ideas or do you need more
info? The former final sql is below.
---------------------------------------------------------------
SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name
FROM tblEmployee AS E LEFT JOIN tblVacation ON E.EmployeeID =
tblVacation.EmployeeID
GROUP BY E.EmployeeID, E.FirstName & " " & [LastName], E.FirstName, E.LastName
HAVING (((E.EmployeeID) Not In (Select tblVacation.EmployeeID From
tblVacation WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtdate]
AND tblVacation.ShiftID = [forms].[frmSchedule].[cboShiftID] )))
ORDER BY E.FirstName, E.LastName;
------------------------------------------------------------------

Try this

SELECT tblEmployee.EmployeeID, tblEmployee.FirstName & " " & [LastName] AS
Name,
FROM tblEmployee
tblEmployee.EmployeeID Not in (Select tblVacation.EmployeeID From tblVacation
WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtrecorddate] )
ORDER BY tblEmployee.FirstName, tblEmployee.LastName
--
I hope that helped
Good luck

I am trying to display a list of employees that do not have vacation on a
certain date. The SQL is below. It kinda of works. The problem is it only
shows the employees that have vacation scheduled, and if they have more than
one date they will be listed multiple times. If you are an employee that
does not have any vacation scheduled your name is not displayed. How do I
show only one employee name and all employees that do not have vacation on
that date?

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name,
tblVacation.VacationDate
FROM tblEmployee AS E LEFT JOIN tblVacation ON E.EmployeeID =
tblVacation.EmployeeID
WHERE (((tblVacation.VacationDate)<>[forms].[frmschedule].[txtrecorddate]))
ORDER BY E.FirstName, E.LastName;

 

[Guest]

Try this

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name
FROM tblEmployee AS E INNER JOIN tblEmpPos ON E.EmployeeID =
tblEmpPos.EmployeeID
GROUP BY E.EmployeeID, E.FirstName & " " & [LastName], E.FirstName, E.LastName
HAVING E.EmployeeID Not In (Select tblVacation.EmployeeID From
tblVacation WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtdate]
AND tblVacation.ShiftID = [forms].[frmSchedule].[cboShiftID])
AND tblEmpPos.Position = [forms].[frmSchedule].[cboPosition]
ORDER BY E.FirstName, E.LastName

Cange the name of tblEmpPos to the name of the table that holde the position
and employee id
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck

Thank you for your help.

That part works and now I need to add one more thing. I would like
cboEmployeeID to only display the employees that are allowed to do that
posistion from cboPosition on the same form. The positions allowed are
stored in a table that uses the employeeID and positionID. An employee can
have more than one position id in the table. Any ideas or do you need more
info? The former final sql is below.
---------------------------------------------------------------
SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name
FROM tblEmployee AS E LEFT JOIN tblVacation ON E.EmployeeID =
tblVacation.EmployeeID
GROUP BY E.EmployeeID, E.FirstName & " " & [LastName], E.FirstName, E.LastName
HAVING (((E.EmployeeID) Not In (Select tblVacation.EmployeeID From
tblVacation WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtdate]
AND tblVacation.ShiftID = [forms].[frmSchedule].[cboShiftID] )))
ORDER BY E.FirstName, E.LastName;
------------------------------------------------------------------

Try this

SELECT tblEmployee.EmployeeID, tblEmployee.FirstName & " " & [LastName] AS
Name,
FROM tblEmployee
tblEmployee.EmployeeID Not in (Select tblVacation.EmployeeID From tblVacation
WHERE tblVacation.VacationDate = [forms].[frmschedule].[txtrecorddate] )
ORDER BY tblEmployee.FirstName, tblEmployee.LastName
--
I hope that helped
Good luck

I am trying to display a list of employees that do not have vacation on a
certain date. The SQL is below. It kinda of works. The problem is it only
shows the employees that have vacation scheduled, and if they have more than
one date they will be listed multiple times. If you are an employee that
does not have any vacation scheduled your name is not displayed. How do I
show only one employee name and all employees that do not have vacation on
that date?

SELECT E.EmployeeID, E.FirstName & " " & [LastName] AS Name,
tblVacation.VacationDate
FROM tblEmployee AS E LEFT JOIN tblVacation ON E.EmployeeID =
tblVacation.EmployeeID
WHERE (((tblVacation.VacationDate)<>[forms].[frmschedule].[txtrecorddate]))
ORDER BY E.FirstName, E.LastName;