Re-Working Current Database

[KristenD]

I have created an employee tracking database that tracks, skill inventories, licensing, certifications, and safety training. After adding tables and different reports and forms for tracking what management wanted, I realized that the database needs reworking. I have to tables that carry virtually the same information with the exception of a few fields. I've worked out on paper what I THINK needs to happen and how to tie it all together correctly relationshipwise.

I have attached the two tables I feel should be joined as well as how they are currently. Now my problem is, and I may be going about it the wrong way, that the tables are tied into the ratings. For example, Joe Smith is rehired at one jobsite and is there for 3 months then is transferred to another jobsite so for each change the jobsite information is filled out. Then in 6 months Joe is laid off. So with this status change the jobsite field should not be filled out. Is there a better way to do this?
Thank you!

 

[KristenD]

I did not realize in the original post that the documents did not attach.

My other thought was that I could link the EmpRating table and EmpInfo table and get rid of some of the fields. Would that be better since some fields will not be filled out if the employee is being laid off?

Thanks for your help!

 

[KristenD]

Can someone help? I am at a loss as to how to proceed.

The original way of thinking is flawed. And as soon as I go to change it, I find another problem logically with the way that will work.

I know that databases are a work in progress but I cannot go forward with anymore additions to the database until I work out this one problem. I'm wondering if I need to create a many to many table in order get done what I need to get done?

 

[andydlindsay]

Hi Kristen

I have attached an example of how you could rework your database using many to many relationships. An employee can work many jobs and a job can have many employees working it... or an employee can have many certifications and a certification can have many employees.

One of the hardest things for me to wrap my mind around when I started DB design was that you shouldn't be concerned with getting the information out of the database when you are designing the tables. Your only concerns should be recording ALL of the information that you need to record and eliminating as much redundant data as possible. Getting the information back out will be a concern for later but, if you structure your tables well, it should be a simple matter to creating queries and reports with all the info that you want.

The question marks after SkillCode in the EmpRating table are because I am not certain about whether your employees are simply being rated or whether they are being rated on a certain skill.

Let me know if this helps you at all.

Andy

 

[KristenD]

THANK YOU SO MUCH!!!!

The way you have it set up makes more sense. I will be adding more sections to the database.

To answer your question regarding the skills, there are 72 skills the employee can be rated on depending on the work he did at the jobsite, also the employee rates himself on all 72 skills. For example, he may only be rated on 8 out of the 72 skills because he only did copper piping for the project.

The certifications are many and varied, forklift training, rigger training, first aid, etc.

My next question is this: I have thousands of records input already into the database. How would you propose getting the records from where they are now in the tables to the way you attached?

Again thank you SO much for your help.

 

[andydlindsay]

My suggestion (and the way that I have done it in the past) is to use Append queries. Design the tables how you would like them designed then query your existing tables for the appropriate info and append to your new tables.

Another suggestion: try not to work within your exisiting database. Copy it or link to the current tables (as boblarson recommended in your earlier thread). You can do a LOT of damage to your data with append or update queries if you are not careful. Make sure to leave the query as a Select query and View (don't Run) your data to make sure that you have everything as you want it before you switch to Append and Run it. You may need to Total (Group By) to eliminate duplicate entries.

I have attached an example db where I recreated tblEmp, tblEmpRating and tblRating. I queried these 3 tables and appended to tblEmpRating2. Once you are certain that all of the information has been added to your new tables, you can delete the links to the old tables, rename tblEmpRating2 to tblEmpRating (for example) and then set about designing your data entry forms!

 

[KristenD]

I have a very stripped down version of the database that I am working with. My only concern with appending the data is making sure that it tblEmpInfo is appending the correct data to the tblEmpRating since there is no direct correlation between those 2 tables.

 

[KristenD]

Ok, after working on this all morning and most of the afternoon, I think I am missing a step. And I am going to have to delete some of the data I believe as well. At the moment I am currently trying to append the data from tblEmpInfo to tblEmpRating with the exception of one field. I want to move the EmployeeType field to the tblEmployee. Also, I am contemplating getting rid of the tblSupt because if I designate the employee in the tblEmployee whether it is Craft or Supervisory, I should be have if the employee is designated as a Supervisor, come up in a drop down box in the form like i currently have the database, I just have to put a code in there correct?
Thank you again for all the help!!