Many to many relationships

[Kelly S]

I'm having trouble creating the correct links/relationships to get my
database to work properly. In a portion of the database, I have a
subcontractor table (basic contact information) and a subcontractor type
table (15 different types of contracting to choose from). I want to be able
to have multiple types for each subcontractor. For example ABC Contractor
can have fire caulking and fire protection as services that they provide; XYZ
Contractor provides demolition and concrete cutting. I've been running in
circles with junction tables and can't seem to get it right. I want to have
a form that when I input new subcontractor info, I can use a drop-down to
select the types of services that apply to that contractor.

Any help would be greatly appreciated.

 

[Jerry Whittle]

You'll need a seperate form or a subform on the Contractor form that is based
on the junction table. You enter in the new Contractor data first. Then in
the form or subform based on the bridging table, you enter the Contractor
foriegn key and the Contractor Type foriegn key. I usually do this with two
combo boxes each with the information from the other two tables as the row
sources.

 

[Kelly S]

Thank you Jerry, that answers part of my question. However, I am having
trouble with getting the junction table right. I can't seem to get my info
to pull together from the different tables. Not sure if is a key problem or
something else. I think I may be setting up the tables/keys wrong.

 

[John W. Vinson]

Thank you Jerry, that answers part of my question. However, I am having
trouble with getting the junction table right. I can't seem to get my info
to pull together from the different tables. Not sure if is a key problem or
something else. I think I may be setting up the tables/keys wrong.

Two comments:

1. If you're trying to copy information from the "one" side tables into the
junction table, DON'T. The junction table should contain *only* two fields -
the foreign key to each "one" side table - unless there is further information
that you need to store about THIS subcontractor with regard to THIS contract
type (e.g. a comment that "he claims to be an electrician but doesn't know
which end of a circuit breaker goes into the box" or "very punctual, shows up
when promised").

2. If you're having problems, please post what you're doing and what problem
you're having.

John W. Vinson [MVP]

 

[Kelly S]

Thank you John, between the two answers I think I've figured out the problem.
I had the right relationship with the junction table but wasn't using the
lookup function correctly to display the name of the field that I wanted.
I.E. I had the subcontractorid key field but wanted to display the
subcontractorname for easier entry/tracking.

 

[John W. Vinson]

Thank you John, between the two answers I think I've figured out the problem.
I had the right relationship with the junction table but wasn't using the
lookup function correctly to display the name of the field that I wanted.
I.E. I had the subcontractorid key field but wanted to display the
subcontractorname for easier entry/tracking.

Don't use the Lookup Field functionality AT ALL. See

http://www.mvps.org/access/lookupfields.htm

for a critique.

Instead, do all interaction with data on a Form, and use a Combo Box on the
form to store the subcontractorID while displaying the name. It's not
necessary to use a lookup field to do this.

John W. Vinson [MVP]