Complicated relationship?

[Elizabeth]

I am trying to figure out the best way to set up a relationship. I have a
table that has job information (ID, address, dates, etc.). I also have a
table set up with tracking codes (electrical, mechanical, plumbing, etc.) and
a third table set up for vendors. I need to list all vendors for each
tracking code for each job.

I'm pretty sure that I need a join table, but I can not figure out how to
accomplish this without creating redundancies. Each job will use every
tracking code, but the vendors will not always be the same for each job and
some vendors belong with multiple tracking codes.

Thanks for any help you can give!

Elizabeth

 

[Jeff Boyce]

Elizabeth

One table for job info ...
tblJob
JobID
Address
...

One table for "tracking codes" (those look like subsystems):
tlkpTracking
TrackingID
TrackingTitle (your "electrical", ...)
... (any other relevent "tracking code" info)

One table for vendors:
tblVendor
VendorID
VendorName
... (other "vendor" info)

And one more table to show the valid combinations of the above three:
trelJobTrackingVendor
JobTrackingVendorID'
JobID
TrackingID
VendorID

This fourth table gives you an easy way to find all of the jobs a particular
vendor is on ... or all of the vendors on a particular job ... or all of the
vendors who provide a particular "tracking code/service"... or ...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP