Not sure how to define relationship ??

[Simon UK]

Hello,

I'm developing a database where I allocate work to a number of contractors based upon their skill i.e. carpenter, electrician, plumber etc. Some of them may have multiple skills.

Question 1:

Should I set up a table that lists the skills for each contractor i.e. one to many relationship

if so, understand how to do that but then getting stuck on the process of how to us this information to allocate the 'job'

The 'job' table has a field that corresponds to carpenter, plumber, electrician etc.

Not sure how to link these?

Thank you in advance.

Regards,

Simon

[comment]

Should I set up a table that lists the skills for each contractor i.e. one to many relationship

It depends: if you ever want to produce a report of contractors by skill, then you do need such table. Otherwise you may get by with a checkbox field in the Contractors table.

[Simon UK]

Hi, I created the 'contractor skill' table so I can select the various skills using a portal on the contractor table but have come across the problem of being able to select the same skill more than once. Is there any way to prevent this ... current fields in table as follows

ID / skillID / ContractorID

linked on one side to skills table and other side to Contractors table.

Can't think of how to prevent duplicate entries via the portal?

Help :???

Regards,

Simon

[comment]

Create another text field with auto-entered calculation (replacing existing value) =

ContractorID & "|" & SkillID

Validate this field as unique.

To prevent validation kicking in (as a cosmetic measure only), you could use a dwindling value list or portal for selecting the skill.

[Simon UK]

Hi,

That's great thank you so much. Was working far too late last night couldn't think and this really helps. I'll be able to use this elsewhere too.

Thanks,

Simon