access database for competancy skills

[fireboxchaser]

Hi

I'm new to this forum and hope someone can help me. I am involved with a steam locomotive, and am trying to construct a crew competancy skills database.

I have everyones contact details in a table and need to be able to list those things they are competant with, as well as add items as they become proficient. I need to be able to query the database and pull out those people then who have a specific competancy skill.

How do I go about this? Running Access 2007.

Cheers

Shirley

 

[neileg]

You have a many to many relationship between people and competancies. You need three tables to represent this in Access.

You need a table of people, one of competancies and a junction table that holds the id of the person and the id of the competancy. Do a search in these forums for many to many and junction tables for lots of help.

 

[fireboxchaser]

Many thanks for the advice. I will take a look and let you know how I get on!

Cheers

Shirley

 

[fireboxchaser]

I have had no luck in deciphering what I need to do to get this junction table done.

Here is the url to a picture of the 2 tables I have created.

http://accessdatabase.fotopic.net/p53637771.html

The top one is 'crew contact details' this needs to be linked somehow to the skills they have a competancy in.

I.e. Shirley Parry is competant in Lighting up, cleaning, disposal.
Christine Kitt is competant in cleaning, oiling up.

I need to know where I list these competancies and how I associate them with members of the crew.

I'm a raw beginner so go easy on me!

Cheers

Shirley x

 

[Rabbie]

I am afraid I cant actually decipher your picture.

but what you need are three tables

tblPeople
PeopleId(PK)
Name
address
etc

tblCompetencies
CompentencyID(PK)
Compentecy
etc

tblPeopleCompetencies
PeopleID(FK)
CompetencyID(FK)

PK = Primary Ke
FK = Foreign Key

 

[fireboxchaser]

Thanks Rabbie.

I think I am a little out of my depth here. Is there someone who would look at my database and sort it out for me if I send it to them?

Cheers

Shirley x

 

[chergh]

This sounds like a skills matrix in which case I would just use an excel spreadsheet.

 

[fireboxchaser]

that would be a solution but I need to run queries if called upon to provide lists of people with particular competancies.

If someone could look at the database I would be very grateful.

Shirley

 

[neileg]

You can attach a zipped version of your database to a posting in here. However, since it's Access 2007 I won't be able to read it!

 

[fireboxchaser]

Hi

I have attached the zipped database. Also there is a compatability pack available for download if someone would look at my database.

It can be found here: http://office.microsoft.com/en-gb/products/HA101686761033.aspx

Thanks

Shirley