Establish a relationship

[CHTHOMAS]

I have a a table called "Contractor" with fields "Contractorid" and "Contractor Name". There are many contractors.

There is another table called "Section" with "Section id" and "section name" fields. The sections are SECTIONA, SECTIONB,....SECTIONG. These sections are same for all the contractors.

eg.

Contractor1 have SECTIONA, SECTIONB, SECTIONC, .....SECTIONG

Contractor2 have SECTIONA, SECTIONB, SECTIONC, .....SECTIONG

Contractor3 have SECTIONA, SECTIONB, SECTIONC, .....SECTIONG

How to represent this relationship instead of entering the same Sections for all the contractors?

Regards

Charley

 

[ghubbell]

Hi Charley,
You will actually need a third table. Here's the whole scheme:

Contractor Table
ContractorID (unique- no dups) 1
Contractor Name & other fields

Section Table
SectionID (unique- no dups) 1
Section Description & what ever other fields you might need.

ContractorSection Table
ContractorID (dups allowed) many
SectionID (dups allowed) many

These two fields can be used to make a compound primary key for this table: One contractor from the contractors table may have many sections from the sections table, but that contractor can only have the same section once.

In filling this info, you might write up a query that would say, append to the ContractorSection Table, all the contractors with all the sections...with a little VB code this could be really easy. Point here is to get the structure right first, and this one is solid.

Voila! ;-)
Gord
gord@ghubbell.com