How do i not delete an employee but not have it show up list

[scott munkirs]

I made an employee database with timesheets and incentive based payroll. Like any business I will have turnover. I have a form that the managers will fill out every Saturday that gives hours and another form that enters incentive in per week. The first field is employee id. When they click on it a drop down menu with all employees are listed (from employee table).



EmployeeID is a numeric id in the employee table. The query i created then uses the employeeid in Name form. The form to enter the above information has a row source with the follwing entry: SELECT Employees.EmployeeID, [firstname] & " " & [Lastname] AS Name FROM Employees ORDER BY Employees.FirstName, Employees.LastName;

This works perfect except for one thing: if I lose an employee I have to delete him from the employee table thus losing the ability to create reports from the past with accuracy. I would like to keep the employee in the table but not have him show up in the drop down menu when selecting employees per week.

I did create a terminated field in the table and made it a yes/no question. what I want to happen is this: if the box is unchecked then the employee is still employeed and will show up on the drop down menu to enter data. If the box is CHECKED, the employee is no longer employeed thus the employee WILL NOT show up on the drop down menu when the manager goes to enter the data per week.

How do I make this happen? I cannot figure it out for the life of me. If you have any other questions please email me at smunkirs@rcn.com. I could send the database to you if you need to see it.

[StepUP]

Just add a Where clause to your query:

SELECT Employees.EmployeeID, [firstname] & " " & [Lastname] AS Name FROM Employees ORDER BY Employees.FirstName, Employees.LastName Where [Terminated] = False

[scott munkirs]

Thanks for the reply. I did it exactly as you stated and typed it and I get the following error:

Syntax Error [missing operator] in query expression 'employees.lastname where [terminated] = false

any ideas?

Scott

[StepUP]

Please post the entire SQL text.

[StepUP]

Sorry, had the where clause in the wrong place:

SELECT Employees.EmployeeID, [firstname] & " " & [Lastname] AS Name FROM Employees Where [Terminated] = False ORDER BY Employees.FirstName, Employees.LastName

[scott munkirs]

Step,

Thanks for the help and so other people can take note of my last question to you I will repeat it here.

I wanted to know how I could create a report witth the past employees after I redid the form and query so that past employees would not show up. This became an issue because after I edited the query all my past data had a blank for the employee Id instead of the past employee.

The solution from step is to create another query that will not take out the past employee. Makes complete sense and should be rather easy to do.

Again, all the thanks to Step for helping me out on this issue.

Scott Munkirs