Better Understanding Required

[Leeroybrown]

I'm in the process of building an Access Database to manage competence of my workforce.

One thing I'm trying to get clear in my head is if you have a primary key say auto number 1,2,3,4,5 does this mean that the same numbers cannot be used as a primary key in another table?

Also within the workforce there a people who's roles are different such as a welder or a pipe fitter but we have minimum training requirements for each role.

Would I use one table for all of the workforce operatives or do I need individual tables for each skill.

Each week I induct new people and I have to check they have all the correct competence certificates the way I'm thinking of designing the database is that when I get a new member of staff I can have a form for new employees with buttons on the form for each type of person such as welder, pipe fitter, electrical tech these all have different training requirements but I want it that when I enter say a new record for the welder the form only opens wih the data fields for the welder.

Can anyone give me any advice on this?

 

[jdraw]

I suggest you work through this tutorial. Doing so will help you with business rules, entities, attributes, relationships and database concepts. You will learn about design which is what your post represents to me.

A Primary key is that field or fields which makes a record unique within a table. A Primary key can not be NULL. A Primary Key can not have duplicate values. A Primary key should exist in each and every table.

You should review Primary Key and Foreign Key.
Here are a few links.
http://msdn.microsoft.com/en-us/library/ms179610.aspx
http://www.programmerinterview.com/...differences-between-primary-and-foreign-keys/

 

[gemma-the-husky]

but -

basically each table is separate - the PK for one table can have the same value as a PK in another. In order to relate item you store the value of another tables PK in the appropriate table

so you have a list of employees with PKJs 1,2,3,4,5,6,7,8,9,10

you have a list of skills, also with PKs 1,2,3,4,5

you relate them in a different table

employeeskills, which store BOTH the value of employee key, AND the values of the skill

thus

employee, skill
1,4
1,6
2,1
2,3
2,5
3,1
3,2

etc

so you can identify the skills that a given emplouee has, but you can equally identify all the employees who have certain skills.

If in your system an employee is employed as a particular tradesman - then you could store that in the employee table. If he has multiple trades you need a separate table

so - one trade

employee 1, jim, trade 4
employee 2, fred, trade 1
employee 3, mike, trade 3
employee 4, bill, trade 4

- several trades, separate table

employee 1, jim
employee 2, fred
employee 3, mike
employee 4, bill

employeetrades
employee 1, trade 2
employee 1, trade 3
employee 1, trade 5
employee 2, trade 1
employee 2, trade 2
employee 3, trade 5
employee 4, trade 6

storing numbers is more efficient than storing the text descriptions as
a) it uses less space
b) you can change the trade description without it affecting all the records

 

[rossjackson01]

Hello Leroybrown

Have a look at AccessLearningZone.com for some free lessons.

Beginner lesson1. It has 12 videos 1 -12.

The next lesson Beginner Lesson 2. This has videos 1 - 8, and costs £0.80p

I'm pretty sure it will give you enough information to answer your questions.

Hope this helps

Regards

Ross

 

[Mihail]

And here is a DB from the beginning to the (almost) end.
Beginner Advice for a Family Database - Access World Forums

 

[smig]

Your questions are very basic.
You should get some learning about the Joined tables idea (Relational db)

I'm in the process of building an Access Database to manage competence of my workforce.

One thing I'm trying to get clear in my head is if you have a primary key say auto number 1,2,3,4,5 does this mean that the same numbers cannot be used as a primary key in another table?

Yes, you can use the same numbers.

Normaly you won't relate them, as you don't relate PK (PrimeryKey) from one table to that of the other table, as this will create a One-One relation which make no sense at all.

Normaly you create a One-Many relations (Like Teacher-Students).
So teachers will be numbered 1, 2, 3, 4, 5... and also students can be numbered 1, 2, 3, 4, 5...

Also within the workforce there a people who's roles are different such as a welder or a pipe fitter but we have minimum training requirements for each role.

Would I use one table for all of the workforce operatives or do I need individual tables for each skill.

You use one table

Jobs - 1, 2, 3, 4, 5....
Skils - 1, 2, 3, 4, 5 ....

Job 1 require Skills 1, 3, 6
Job 2 require Skills 2, 4, 5
Job 3 require Skills 7, 8

The table that will tell what each Skills are required for each Role will look like:
Job, Skill
1, 1
1, 3
1, 6
2, 2
2, 4
2, 5
3, 7
3, 8
if you add a new skill (9) that will be required by Jobs 2 and 4 it will be added to the same table as:
2, 9
4, 9


If each skill require several SubSkills you will add another table Skil_SubSkill:
Skill, SubSkill
1, 2
1, 3
2, 4
3, 5
3, 5
3, 1

 

[smig]

...
b) you can change the trade description without it affecting all the records

I agree with everything gemma-the-husky say
but this last line should be used carefully as it will affect all the history too