New Database, trying to determine keys and relationships

[joe]

Hello All

I am struggling to create a new database structure that gives me what
I am looking for. Seems like it should be easy at first glance but I
have not figured out how to get what I want yet. Here is the problem.

I have information about projects, contractors and skills. For each
project I have I would like to create a query that displays all the
possible skills that a job could need. And then for every project I
would like to show of all the skills I have, what contractors have I
found to fulfill the need.

I started creating 3 separate tables; Projects, Skills, Contractors
and the subsequent fields I needed. Don’t know why I am so stuck here
but not sure how I can configure this so that I can list 1 project,
all skills and then any contractor(s) that meet my need for that
particular project.

Seems like I need one primary key that is common to all tables and
that allow a 1- many relationship between Projects and Skills and 1-
many for skills and contractors.

Aside from the basic table fields, can anyone provide some thoughts on
how I can structure the keys and relationships to get started?

Many thanks

 

[Beetle]

The following example table structure assumes the following;

1) A Project can have one or many related Skills.

2) For each Skill in a given project you want to select one
qualified Contractor to perform that Skill for that Project.

3) A given Skill can be performed by more than one Contractor, and
a given Contractor can perform more than one Skill, hence a
many-to-many relationship between those two entities (so you
need a junction table).

PK = Primary Key, FK = Foreign Key

tblProjects
********
ProjectID (PK)
ProjectDescription
StartDate
other attributes of the overall Project

tblProjectDetails
************
ProjectDetailID (PK)
ProjectID (FK to tblProjects)
SkillID
ContractorID
other attributes of ProjectDetails

tblSkills
******
SkillID (PK)
SkillDescription
other attributes of a Skill

tblContractors
**********
ContractorID (PK)
ContractorName (if it is a company. Use FirstName and LastName for a person)
other attributes of a Contractor

tblContractorSkills (the junction table)
*************
SkillID (FK to tblSkills)
ContractorID (FK to tblContractors)

The above table would use SkillID an ContractorID as a composite PK.

To populate tblContractorSkills appropriately you would, for example,
have a main form based on tblSkills with a subform based on
tblContractorSkills with SkillID as the Master/Child link. In the subform
you would use a combo box bound to ContractorID to select all the
Contractors that can perform that Skill. This combo box would use a
row source query that pulls data from tblContractors. This form would
list all the Contractors that can perform a given Skill. Likewise, if you
reverse the logic, you could have a main form based on tblContractors
with a subform based on tblContractorSkills which would list all the
Skills that a given Contractor can perform.

For your Projects, you would have a main form based on tblProjects
with a subform based on tblProjectDetails with ProjectID as the
Master/Child link. In this subform you would use combo boxes for
selecting the Skill and the Contractor for each record. The first combo
box (cboSkills) would have a row source that pulls data from tblSkills.
The other combo box (cboContractor) would use a query that joins
tblContractorSkills and tblContractors and the returned records would
be limited by the value that is selected in the first combo box (cboSkills).

This may not be exactly what you need, but hopefully will at least give
you an idea how to proceed.