One table too many!

[TeeSee]

The following was a suggestion as to how to properly set up tables
utilizing a "linking" table = tblCompanyContacts. I can see the
obvious linking but am totally at a loss as to what the logic would be
in obtaining just the contacts for each specific company. I can get
the result I want using just the two tables Company and Contact with a
fkCompany in the contacts table, but I DO want to understand the
concept of the three tables. Help appreciated always.

Companies
CompanyID <primary key>
<name, contact information, identifying information for the
company>

Contacts
ContactID <primary key>
<Name etc identifying information for the
contact>

CompanyContacts
CompanyID <field 1 of two-field primary key>
ContactID <field 2 of two-field primary key>
<maybe other fields pertaining to THIS Company with THIS contact>

 

[Stefan Hoffmann]

hi,

I can get
the result I want using just the two tables Company and Contact with a
fkCompany in the contacts table, but I DO want to understand the
concept of the three tables.

CompanyContacts
CompanyID <field 1 of two-field primary key>
ContactID <field 2 of two-field primary key>
<maybe other fields pertaining to THIS Company with THIS contact>

You have this kind of relationship:

Company 1:m CompanyContacts n:1 Contacts

Which is in fact a m:n relationship between Companies and Contacts:

Company m:n Contact

"one company has 0, 1 or more contacts AND one contact belongs to 0, 1
or more companies1."


mfG
--> stefan <--

 

[BruceM]

A typical use of a linking table is with courses in an academic program.
Each course may include many students (i.e. more than one), and each student
may be in many courses. A junction table is needed to resolve the
many-to-many relationship. With a junction table you can see a listing of
courses that each student is taking, and a list of students in each course.
In your situation a junction table would be needed if a contact could be
associated with many companies. You could still use a junction table even
if a contact is associated with just one company. It would not gain you
anything I can think of in terms of display of the data, but it wouldn't do
any harm, and it would be the safest course if you can anticipate a
situation in which one contact could represent several companies (a
consultant or accountant or something like that, or maybe there would be one
contact for a given position in several associated companies).

 

[TeeSee]

hi,


You have this kind of relationship:

   Company 1:m CompanyContacts n:1 Contacts

Which is in fact a m:n relationship between Companies and Contacts:

   Company m:n Contact

"one company has 0, 1 or more contacts AND one contact belongs to 0, 1
or more companies1."

mfG
--> stefan <--

Gentlemen .... Thanks for those explanations, both of which are very
helpful in understanding the logic between tables. The exact spot that
is troubling me at the moment is as follows .....
If I was to code SQL in a combo box for instance with my two table
scenario I think I would say something like "SELECT * FROM tblContacts
WHERE CompanyId = CompanyID since there is a CompanyID field in the
Contacts table.
How would I code the same using the three table concept?

 

[TeeSee]

hi,


You have this kind of relationship:

   Company 1:m CompanyContacts n:1 Contacts

Which is in fact a m:n relationship between Companies and Contacts:

   Company m:n Contact

"one company has 0, 1 or more contacts AND one contact belongs to 0, 1
or more companies1."

mfG
--> stefan <--

Stefan, Sorry but I have to ask in M:N ... what does the N stand for?

 

[Stefan Hoffmann]

hi,

Stefan, Sorry but I have to ask in M:N ... what does the N stand for?

Both, m and n, describe the cardinality as simply any natural integer
greater zero.



mfG
--> stefan <--

 

[BruceM]

In your SQL you are looking for the records from tblContacts in which the
CompanyID field is equal to some value. If that value is the field
CompanyID in the current record you may have something like this:

Dim lngCoID as Long
lngCoID = Me.CompanyID

"SELECT * FROM tblContacts WHERE CompanyId = " & lngCoID

This assumes CompanyID is a number. If it is text you would substitute for
lngCoID:

Dim strCoID as String
strCoID = Me.CompanyID

"SELECT * FROM tblContacts " & _
"WHERE CompanyId = " " " & strCoID & " " " "
or
"SELECT * FROM tblContacts " & _
WHERE CompanyId = ' " & strCoID & " ' "

Spaces between the quotes are added for clarity.

Then you would do something with the SQL, such as use it as the Row Source
for a combo box.

The third table does not affect the SQL as such. Essentially you are
creating this SQL, assuming CompanyID is 49:
"SELECT * FROM tblContacts WHERE CompanyId = 49"

If the 49 (or whatever the value) is coming from something other than the
current form you need to find a way to use that value as lngCoID or strCoID.
Details depend on your particular situation.

hi,


You have this kind of relationship:

Company 1:m CompanyContacts n:1 Contacts

Which is in fact a m:n relationship between Companies and Contacts:

Company m:n Contact

"one company has 0, 1 or more contacts AND one contact belongs to 0, 1
or more companies1."

mfG
--> stefan <--

Gentlemen .... Thanks for those explanations, both of which are very
helpful in understanding the logic between tables. The exact spot that
is troubling me at the moment is as follows .....
If I was to code SQL in a combo box for instance with my two table
scenario I think I would say something like "SELECT * FROM tblContacts
WHERE CompanyId = CompanyID since there is a CompanyID field in the
Contacts table.
How would I code the same using the three table concept?

 

[Beetle]

To answer your question about how to handle this, you would do
so through the use of forms. You would have a main form based
on, for example, the Company table with a subform based on the
junction table (CompanyContacts). The Master/Child link between
the forms would be CompanyID. In the subform you would use a
combo box bound to the ContactID field (from the junction table)
with a row source that selects ContactID and ContactName from
the Contacts table.

This would display the Company name in the main form, and all
the associated Contacts in the subform. You could also have another
form that would display the Contact in the main form and all the
associated Companies in the subform.

 

[TeeSee]

In your SQL you are looking for the records from tblContacts in which the
CompanyID field is equal to some value.  If that value is the field
CompanyID in the current record you may have something like this:

Dim lngCoID as Long
lngCoID = Me.CompanyID

"SELECT * FROM tblContacts WHERE CompanyId = " & lngCoID

This assumes CompanyID is a number.  If it is text you would substitutefor
lngCoID:

Dim strCoID as String
strCoID = Me.CompanyID

"SELECT * FROM tblContacts " & _
"WHERE CompanyId = " " " & strCoID & " " " "
or
"SELECT * FROM tblContacts " & _
WHERE CompanyId = ' " & strCoID & " ' "

Spaces between the quotes are added for clarity.

Then you would do something with the SQL, such as use it as the Row Source
for a combo box.

The third table does not affect the SQL as such.  Essentially you are
creating this SQL, assuming CompanyID is 49:
"SELECT * FROM tblContacts WHERE CompanyId = 49"

If the 49 (or whatever the value) is coming from something other than the
current form you need to find a way to use that value as lngCoID or strCoID.
Details depend on your particular situation.











Gentlemen .... Thanks for those explanations, both of which are very
helpful in understanding the logic between tables. The exact spot that
is troubling me at the moment is as follows .....
If I was to code SQL in a combo box for instance with my two table
scenario I think I would say something like "SELECT * FROM tblContacts
WHERE CompanyId = CompanyID since there is a CompanyID field in the
Contacts table.
How would I code the same using the three table concept?- Hide quoted text -

- Show quoted text -

A sincere than you Bruce. That was the clarification I needed. Best
regards

 

[Beetle]

In the many-to-many relationship structure you originally
described (and if that is the structure that really applies
in your circumstances), the Contacts table *will not have*
a CompanyID field, so you would not write a statement like;

"Select ContactName From tblContacts Where CompanyID = x"

 

[TeeSee]

In the many-to-many relationship structure you originally
described (and if that is the structure that really applies
in your circumstances), the Contacts table *will not have*
a CompanyID field, so you would not write a statement like;

"Select ContactName From tblContacts Where CompanyID = x"
--
_________

Sean Bailey






- Show quoted text -

Thanks Beetle ... That was the part I had overlooked and forgotten
about already. That the linking table is required to cope with the
possibility of a Many to many situation.