Database Design to Maintain Inactive Employee Records

[Tylin]

Hello!

I am designing an Access database with a table linked to Excel that contains employee data. This data is updated regularly and contains thousands of employees.



My question pertains to employees who are no longer with the company.

I track interactions with employees, including their name, email address, department, etc.

This information is looked up in the Excel linked tab, using the employee number the PK field.

The user opens a form, selects the employee number from a combo box that refers to the Excel linked table. The additional information - name, etc. is auto-completed from the linked table based on the employee number.

My question is - how do I deal with employees no longer with the company? I need to keep the records of interactions with them.


The choices, as far as I see, are one of the following:

1. Add some code to the Excel file so that updated employee data includes a field called Active/Inactive and retains all employees, even those no longer with the company.
2. Do not include employees no longer in the company in the Excel file. Add the name, email, department, etc. to tblInteractions in the Access database in order to keep interactions for all employees, regardless of if active or not.

Any help/suggestions are greatly appreciated!

[CJ_London]

Add some code to the Excel file so that updated employee data includes a field called Active/Inactive and retains all employees, even those no longer with the company.

what does active/inactive mean? usually I would expect to see a 'leave date' field. If the employee returned, they have a new employeeID and record - because for tax purposes, the employer would have issued a P45 or equivalent on leaving.

If you regularly have employees leaving and returning, then that information should be stored in a separate table

[isladogs]

Use option 1 - boolean field called Active - true by default - NEVER delete data

OR if you have a field called LeavingDate you can use that instead
Active employees will have a LeavingDate=Null, leavers will have a value in that field

EDIT : Ajax got there a bit faster!

[Tylin]

Inactive would mean the employee is no longer with the company. Taxes etc. are not a concern in this case. I would say employees do not regularly leave and return.

The employee data is updated regularly. The new data will not include employees that are no longer employed. I would have to have Excel evaluate whether employees are included in the new data and, if not, add a field that indicates they are no longer active/employed.

[Tylin]

The employee will simply not be included in the new data. There is no LeavingDate - they will just not appear. I could always add a field, though, that would track whether an employee is in the updated data; if not, we could indicate Active False, for example.

My question was also - do I always leave all employees in the Excel file (active or not) and use that table, or copy name, department, etc. to the tblInteraction in Access so that if they are no longer in the Excel data, it won't matter - I will still be able to see the history for that employee, whether still active/employed or not.

Does that make sense?

[CJ_London]

I would add a leaving date field to your table and populate it with an update query using a suitable date (might be today, might be week ending/starting) and a left join on employeeidfrom your table to the excel data and criteria where the excel employeeid is null

My question was also - do I always leave all employees in the Excel file (active or not) and use that table, or copy name, department, etc. to the tblInteraction in Access so that if they are no longer in the Excel data

that depends on your process - are you updating it? someone else or some hr system? who else uses the spreadsheet?, what do you do with the data? etc. Linking active data to excel is generally not advised due to performance issues - datatypes can vary (e.g.someone puts some text in a numeric field in excel), you can't add indexing, etc), better to copy the data into access tables where you can maintain control and export back to excel if required.

[Tylin]

There are a few users who will use the database. The data from Excel is used to look up the employee name, department, etc. We will record interactions with employees by selecting their EmployeeNo from the Excel data. Other fields that are not in Excel will be filled in by us - best way to contact them, notes about the interaction, date of the interaction, etc.

We will want to keep track of interactions by employee, interactions of one of the people using the database, how many interactions by employee, etc.

The Excel data updates are downloaded from an HR system.

Most information will stay the same - name, email, although department, title and location may change.

I was concerned about the performance lag using Excel linked table. I suppose I can import then use an update query. If various information is updated, such as department, it will modify it. If the employee doesn't exist anymore I could add a date field as you suggested above, or use a field to denote Active or Inactive.

I like that idea. I've not used Excel linked tables much for uploading data and, as you mentioned, find them lacking for field properties, etc.

I can also create a query that would exclude Inactive employees from the employeeNumber combobox from which we will select it in the form to populate the other fields which will help with performance.