adding Term date or Active marker to employee table

[Nymandus]

I have an active database that is used to evaluate employee call performance. When building the database (my first full attempt) I did not include a way to sort out employees who are no longer active.
I know I dont want to delete them.

I have three thoughts to fix this but would like opinions.

1) Add a termination date to the employee table

• How do I set my form to ignore all employees with a term date? (I currently have a drop down box that allows us to choose the associate from the employee table by using the Row Source)
• What if the employee were to return to the position (has only happend about 5 times in 15 years)
• would sorting termed employees out of reports be as simple as adding in the criteria of the query something like "is Null" under the term date?

2) Add a radial button to the employee table to show "active"

• Same question about how to ignore employees that have been unclicked?

3) Add a Z-to the last name of the (employee name) this will drop them to the bottom of the list.

• What happens to the associated records under the "old" employee name?

any thoughts or suggestions woudl be greatly appreciated.

 

[pbaldy]

1 would work, and it would be as simple as using "Is Null" in the criteria. 2 would work, presuming you mean a yes/no field. I wouldn't do 3, particularly if name was the primary key, which it should not have been (potential duplicates, names changing due to marriage, etc). Regarding 1 and returning employees, we have that situation. I have a related table for hire/term dates related one-to-many on employee number. I can get active employees by querying that table with Is Null on the term date field.

 

[Cronk]

My preferred method is the addition of a Yes/No field called Active, in as well as to Exit date, if needed.

 

[Nymandus]

Ok, so we will continue this with OOPS...:banghead: I did set the Employee name to Primary Key. I can add an employee number at the time I work on adding the term date but now I need to know the pitfalls..

first on the term date.

• You mentioned having a related table of hire/term dates
  • so this table will have employee number as primary key, hire date and term date as it's 3 fields?
  • then in my employee table I would use look-up list box? how would that work with dates since it is a fluid set of data?

If I add an employee number field and set that to Primary in my employee table:

• will it allow me to make the change?
• If I have my queries pointing to the Employee name will that need to change?
• will this cause a cascade problem with all my reports and queries?
• what is the effect on my current data (evaluations already in the database, over 3000 at this point) if I make this change?

 

[pbaldy]

Re: then in my employee table I would use look-up list box? how would that work with dates since it is a fluid set of data?

Not clear on the question, but there wouldn't be anything related to dates in the employee table. They would all be in the related table. I use a form bound to the employee table with a subform bound to the dates table.

As to changing the key to an employee number, you would add the new field to the employee table and any related tables. Populate it in the main table, then run update queries to populate it in each related table, with a join on the name. Then you should be able to change the key fields and relationships to use the numeric field. Existing queries that join on the name would need to be changed. Not necessarily simple, so it's a question of whether you want to go forward properly or let sleeping dogs lie.

 

[Nymandus]

thank you,

I was thinking the same thing...sleeping dogs my be better left that way...

as this was my first database, I will adapt and my next database will be better...

as we dont have a huge flow of employees we may have the rare occasion of the same name, in which case we will use a middle initial with the name when the time comes.

 

[pbaldy]

No problem, just keep it in mind for later when they say "reports for Mary Jones (formally Mary Smith) aren't working correctly any more".

 

[Cronk]

Paul, the issue you raise is easily fixed by hiring another person with the name Mary Smith. The report will work fine; pity the contained information will be wrong.

 

[pbaldy]

I should have mentioned that Cascade Updates can overcome the name change problem, but using a number is still the preferred method.

That said, I've spent a lot of time over the last 2 weeks dealing with a situation in which my employer decided to change a numbering system that has been in place for many many years. It has been a nightmare. My position on the natural vs surrogate key debate may be changing (though in this case the numbering originates in a canned payroll program outside my control).