Create a table from 2 other tables

[Cyclops]

I haven't worked with dbs for many years and therefore should be viewed as a newbie.
Perhaps there is already an answer to my question, but I have not found it so here goes.
Access 2010
I have a table with 200 people on it. I have another with 400 'skills' on it. Each person can have a few or many skills, whist each skill can be had by many people.

I want to list either all the skills each individual has, and/or each skill held by all indiviiduals.
Eg Fred has skills 1,4,5,18, etc, and skill number 2 is carried out by fred, mary harry etc.



I can only see that I need to create 200 tables, and list all the skills of that person, or 400 tables and list all those qualified.
This seems a lot of tables. (if that is necessary then so be it, I'll do it)
Can you experts please advise and the best approach?

Many Thanks
Phil

[alansidman]

You should join your two tables in a query and list each individual and their skills. Show us your table layouts and I am sure that someone here will be able to give you the SQL statement necessary.

[ArviLaanemets]

Your table for people must have an ID (Primary Key) field. The easiest way is to have autonumeric field as ID field, but in case in your country all people have an Personal ID Code, and you need to use it in your DB, then you can have it instead.
tblPersons: PersonID, Forename, LastName, ...;
Your table for skills must have an ID (Primary Key) field too.
tblSkills: SkillID, Skill, ...

You need a table, where you can link skills to persons.
tblPersonSkills: PersonSkillID, PersonID, SkillID
,where PersonID and SkillID must compose an unique index (a person can have certain skill attached only once).

In case additional skills may be attached to person over time but all skills attached earlier remain active, and it is does matter when every skill is attached (skills history matters), the structure will be:
tblPersonSkills: PersonSkillID, PersonID, SkillID, ActiveAt
,where PersonID and SkillID must compose an unique index (a person can have certain skill attached only once), and ActiveAt is date when skill was attached.

In case additional skills may be attached to person over time but some skills attached earlier my be grouped and ranked in group, so only single skill from group may be active at any given time, and it is does matter when every skill is attached (skills history matters), the structure will be:
tblPersonSkills: PersonSkillID, PersonID, SkillID, ActiveAt, InactivAt
,where PersonID and SkillID must compose an unique index (a person can have certain skill attached only once), ActiveAt is date when skill was attached, and InactivAt is date when skill was set inactiv.

There may be more complex structures for this table, but probably you got it.

[Cyclops]

Thank you
This is basically what I expected, with one point I have difficulty getting my head around.
How would I actually enter the data? Do I have one skill and then enter all those with that skill?
Would that not mean that if Fred has 20 skills, I enter his ID 20 times and the skills once against each entry, or the reverse?
Enter one skillId 20 times and 20 different PersonId's it still seems to indicate that I need to link every skill/person multiple times, creating a large number of tables.
I appreciate your input
Phil

[CJ_London]

Lets put Avrils suggestion another way

you need 3 tables

tblPeople
PersonPK autonumber
PersonName text
....
....


tblSkills
SkillPK autonumber
SkillName text

and to join them

tblPeopleSkills
PeopleSkillPK autonumber
PersonFK long - links to personPK in tblPeople - set in relationships
SkillFK long - links to skillPK in tblSkills - set in relationships

How would I actually enter the data? Do I have one skill and then enter all those with that skill?

With this table design you would do the following
create a single form for tblSkills
create a continuous form for tblPeopleSkills - in form design remove the controls for peopleskillPK and SkillFK and change the PersonFK field to a combo with a rowsource of tblPeople - bound to column 1 and formatted with two columns and a column width of 0.

now open your form for tblSkills and drag the tblPeopeSkills form on to it

All done, no code required.

to use, open the tblSkills form, for each skill you can just select the relevant people

You can do the same to allocate skills to people by having a form for tblPeople and a different version of the tblPeopleSkills form where you remove the peopleSkillPK and the personFK controls and change the skillFK control to a combo with a rowsource of tblSkills.

There is a lot more you can do with the tables forms - add a search facility for example, or add a field or fields to tblPeopleSkills around when skilled from and/or qualification levels. Or to tblPeople around availability and working conditions (i.e. doesn't like heights)

[Cyclops]

I'm still having trouble. I followed the instructions, I think. But in the combo box I only get the first name and no way to select anyone. I tried a query and the names list populated but still could not select any against a particular skill. I am floundering.

[CJ_London]

suggest zip and upload your db

[Bob Fitz]

I'm still having trouble. I followed the instructions, I think. But in the combo box I only get the first name and no way to select anyone. I tried a query and the names list populated but still could not select any against a particular skill. I am floundering.

Can you post a copy of your db

[ArviLaanemets]

Simplest example is attached.

In case you go for single skill form with continuous persons subform, the RowSource for person combo must be like

SELECT tblPesons.PersonID, Trim(tblPesons.Lastname & " " tblPesons.Forename) AS Fullname FROM tblPesons ORDER BY 2;