Simple "Lookup" question (hopefully)

[Lobster1071]

I believe this answer is obvious, but just wanted to see if there was a better/easier way that I was missing.

I have a main table called "Assignments" (Date, Time, EmployeeID, Notes, etc). The EmployeeID field is a Lookup field (combo box) which of course is linked to a simple "Employee" table with two fields (ID and Employee Name).

The problem is, if I ever delete a record from the Employee table, the Employee gets deleted from the Assignments table as well. I would like to keep that name in the Assignments table even if the employee gets deleted from the linked table.

Is this simply a matter of adding a field called "Employee Name" to the Assignments table? Whenever I select an employee for the assignment in a form, I would tell that field (which is not linked to the Employee table) to get updated?

Couldn't find this answer poking around the forums, hopefully someone will tell me an easier way to do this if possible. (Maybe a Lookup field isn't even neccessary, and I could just create an Employee Name field anyway, then select the employees from a "stand-alone" combo box in a form).

 

[HiTechCoach]

Why would you want to delete the record form the Employee table?

What you are asking to do is to create orphaned records. I would urge against this.

I normally use a Yes/No field for Active. When adding a new project, filter the combo box to select employees to only the records that are marked Active.

It also sounds like you have defined the relationship between the tables to have cascading deletes. I never use this. I have found it to cause more problems that it is worth.

 

[wiklendt]

i agree with HiTechCoach. use an 'active' checkbox for your employees. i presume you mean to delete employees who no longer work there, and so won't receive any more assignements? HTC offers an elegant solution.

 

[Lobster1071]

Thank you very much for the responses, and I like that suggestion using the filter. It should work out just fine.

Thanks again.