A new layer to Employee Experience

[jk42]

Hi!

Sigh, Another day, another new Access question. I have an Employee Table that's related to my Employee Experience table. That's working out beautifully. The new issue I'm facing is how to add another level to the Experience table.

Right now I track their major category experience, such as
"Project Management."
"Human Resources"
Etc.

But what i want to do, is add specifics to each of those categories, and be able to search by either or. Plus, each category and sub category will have a check box that will indicate if it's level one-level four.

How would I do that? Could I do this? I honestly have no clue where to start. I want it to be:

Employee: JK42
Experience: Human Resources
-Problem Solving
-Data Entry
Project Management
- Industrial Engineering
-Mechanical Engineering

I will have about 50 "major" categories, and each of those major categories could have 100s of sub categories.

Any help is SO much appreciated.

 

[plog]

You kind of have to break what you have now and start over--your EmployeeExperience table (which I hope you have) is obsolete. It will be replaced by a new table that links Employees to ExperienceSubCategories. You will have 4 tables to do this:

Employees
EmpId, EmpFirstName, EmpLastName, EmpStartDate
JK42, John, Smith, 5/2/2013

ExperienceCategories
ExpCatID, ExpCatDesc
1, Human Resources
2, Project Management

ExperienceSubCategories
ExpSubCatID, ExpCatID, ExpSubCatDesc
1, 1, Problem Solving
2, 1, Data Entry
3, 2, Industrial Engineering
4, 2, Mechanical Engineering

EmployeeExperience
EmpID, ExpSubCatID
1,1
1,2
1,3
1,4

Those are the minimum fields and tables you need to accomplish this. I used the sample data you provided to display how it will go into the tables. Look it over and if you have any questions let me know.

 

[jk42]

Thanks! I *think* I will be able to make this work. The only question I have right now is the description field. Do I really need to have a description in there?

I'll be giving this a go tomorrow afternoon and will let you know if I get stuck. Thank you SO much. I appreciate it very much!

 

[plog]

If you look at what the Description fields align to, yes. I gave the fields that hold the text portion of your data that name (i.e. "Problem Solving" and "Human Resources")

 

[jk42]

Ok, I am really confused trying to implement this. Which tables do I create relationships between? I think that where I lose it is

ExperienceSubCategories
ExpSubCatID, ExpCatID, ExpSubCatDesc
1, 1, Problem Solving
2, 1, Data Entry
3, 2, Industrial Engineering
4, 2, Mechanical Engineering

I don't understand how this table works. So for each experience, I have to add a row with a sub category?
So if there are 15 associated sub categories with Human Resources, I have to have 15 rows that are:
1,1,blah1
2,1,blah 2
3,1,blah 3
4,1,blah 4

Or am I way off in my interpretation? If that's how it has to be, I may try to convince the person that requested this to NOT do it this way, as the combinations could potentially take WAY more time than it's worth to put together.

Thanks so much!

 

[jdraw]

You may want to review this tutorial to get a clearer understanding of Normalization and relationships.
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[plog]

Which tables do I create relationships between?

They are all related, although not all directly:

ExperienceCategories links to ExperienceSubCategories via ExpCatID
ExperienceSubCategories links to EmployeeExperience via ExpSubCatID
Employees links to EmployeeExperience via EmpID

So for each experience, I have to add a row with a sub category?
So if there are 15 associated sub categories with Human Resources, I have to have 15 rows that are:

First, be particular in your words. The word 'experience' is now ambiguous, do you mean a record in ExperienceCategories or ExperienceSubCategories? What do you mean by experience?

Second, if there are 15 subcategories to Human Resources experiences, you would have 15 records in ExperienceSubCategories.

 

[jk42]

Oh ok. I may be over thinking this... I guess I just need to implement it, and see how it works before I'll really get it. It doesn't matter how many times I read articles or examples, it won't click until I see it working in my own database. Thanks so much for all of your input. Hopefully, I'll get it this time around. (Doubtful, but I'm thinking positively!)

 

[jk42]

Hi!

Ok, so I don't know what I don't understand, or what I did wrong... but I can't get it to work at all. If you have the time, could you give me the most dumbed down explanation possible? When I was trying to enter in the Employee's sub experience in this table:
EmployeeExperience
EmpID, ExpSubCatID
1,1
1,2
1,3
1,4

It wouldn't let me do an employee more than once. Aargh! I really am just lost. I went into relationships and made the relationships that you explained in the last post.
THanks for your help!

 

[plog]

Sounds like EmployeeExperience has a primary key (specifically EmpID). It should not have a primary key, just those 2 fields. EmployeeExperience is a junction table (http://en.wikipedia.org/wiki/Junction_table) which means it exists to create many-to-many relationships.

 

[jk42]

Wow. I now see that this is not difficult! I don't know what I was thinking! Maybe I needed a day away from it to see it, but it makes sense now!! Thanks SO much, I really appreciate it!