Completely Lost

[007stuart]

I have a problem that I am sure is easy to resolve but is beyond me. I have a number of housing developments in many parts of the UK that I manage and have many contractors each with differing skills that cover not all the the developments.

I have created a many to many relationship between the the 2 tables (TblDevelopments and TblContractors)

I now want to be able to choose the correct Contractor for a particular development for a specific repair

Tbldevelopments contain only the development name.

TblContractor contains Contractor Contact information and 5 yes/no boxes for Landscaping/Plumbing/TV & Satellite/Electric Gates/Pest Control.

I have a form that records the repair request date, follow up date, and expected completion date (these are all bound to TblRepairs) I have an unbound Combo Box that allows the type of repair required to be recorded.

What I need to do now is have access select the correct contractor(s) to be intructed to carry out the repair.

Can anyone suggest my next step?

 

[wazz]

i think i would consider another table or two:

- tblJobs (Landscaping/Plumbing/TV & Satellite/Electric Gates/Pest Control)
- tblContractors_Jobs (N:N)

instead of this many:many relationship
tblDevelopments : tblContractors

do
tblDevelopments : tblContractors_Jobs

then you can link a row from tblContractors_Jobs to tblDevelopments and you will know exactly which contractor you are dealing with.

and with tblContractors_Jobs you can link each type of job to specific contractors. you will then know what jobs they can do and can do away with all of the yes/no fields in tblContractors.

 

[007stuart]

Hi Wazz,

Thanks for your help.

What does "(N:N)" mean after - tblContractors_Jobs?

Likewise "do" before tblDevelopments : tblContractors_Jobs

Regards


Stuart

 

[wazz]

What does "(N:N)" mean after - tblContractors_Jobs?

many to many relationship

Likewise "do" before tblDevelopments : tblContractors_Jobs

just meant, instead of this relationship:
tblDevelopments to tblContractors (many to many)

create this relationship:
tblDevelopments to tblContractors_Jobs

not sure that makes sense. what i mean is, create this first:

tblJobs -> tblContractors_Jobs <- tblContractors
- this will link contractors to jobs. tblContractors_Jobs will hold the info telling you which jobs a contractor can do.

then link
tblDevelopments -> tblContractors_Jobs
- this will link developments to the central table above. the central table tells you at a glance which jobs have contractors 'assigned' and, inversely, which jobs a contractor is able to do.

btw, this is not absolutely necessary, but without it, you could enter a contractor into a development's job when the contractor can't actually do the job. hth.

 

[007stuart]

Hi Wazz,

Thanks for your reply, it makes a lot more sense.

Regards

Stuart