Many to Many Relationship- help

[Amps]

I have two tables that have a many to many relationship. I have 2 questions

i) When i created the relationship between them in access, the relationship
type is listed as "intermediate". what does this mean?

ii) I was told that access will NOT allow many-many relationship...but i was
able to connect these two tables and write a query to match the data. what am
I missing?

Thanks for your help.

 

[Lynn Trapp]

i) When i created the relationship between them in access, the

relationship
type is listed as "intermediate". what does this mean?

You might want to check to see if that relationship type isn't listed as
"indeterminate" instead of "intermediate."

ii) I was told that access will NOT allow many-many relationship...but i
was
able to connect these two tables and write a query to match the data. what
am
I missing?

It's not possible to build a many-many relationship between 2 tables. It
requires a third table that serves as a "junction table" between the other
2.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[Immanuel Sibero]

Hi Amps,

i) When i created the relationship between them in access, the relationship
type is listed as "intermediate". what does this mean?

You mean *indeterminate*? As the term suggests, an indeterminate
relationship is one that can not be determined (i.e. within the context of
whether one-to-many, many-to-many, etc.)

ii) I was told that access will NOT allow many-many relationship...

Many-to-many is implemented as two or more *one-to-many* relations. This is
done by creating a *link* or *junction* table. This is true in Access or any
other relational-based DBMS.
So, strictly speaking, we could say Access or any other DBMS do not allow
many-to-many relationships, they only allow one-to-many. But you can
implement (i.e. facilitate) many-to-many by using multiple one-to-many's.

but i was
able to connect these two tables and write a query to match the data. what am
I missing?

Well, you can connect any two tables using any two fields in Access, whether
or not it is one-to-many or many-to-many, or indeterminate is another
matter. As a matter of fact, the reason why you get an *indeterminate*
relationship is because you simply connect any two tables.



HTH,
Immanuel Sibero

 

[mnature]

As an example of how to do a many-to-many relationship:

tbl_Customers
CustomerID (PK)

tbl_Items
ItemID (PK)

tbl_CustomerOrders
CustomerOrderID (PK)
CustomerID (FK)
ItemID (FK)

You want to keep track of customer orders, so you have a table of customers
and a table of items that they can order. There is no logical way to link
the two tables directly. So a linking table is used, which has foreign keys
from both of the tables you want to link. Both the customers table and the
items table have a one-to-many relationship with the customer order table.
Any customer can be linked to numerous items, and any item can be linked to
numerous customers.

 

[Amps]

You are correct. Its indeterminate. Your replies make sense to me. I have a
related question.
What if I cannot create an intermediate table to link two tables (that have
many-many relationship). In that case, should I try to combine the tow tables
(there will be some redundant info). Many thanks

Amps

 

[Immanuel Sibero]

Amps,

I'm not sure I'm understanding your question. Maybe specific examples of the
tables you are working on would help. In the meantime, I'm going to
backtrack a little to relational theory (normalization) which maybe the
issue here.

Before you even worry about whether two tables are related one-to-many or
many-to-many, they (i.e. the tables) should first be normalized. Once your
tables are normalized, then the tables themselves will determine whether
they're related one-to-many or many-to-many amongst themselves.

If you find yourself looking at two tables and thinking *hmmm.... wonder if
these two tables are related one-to-many or many-to-many or are they related
at all??*. Worse yet, if you're thinking *Should I COMBINE these two
tables??*. My bet would be that your tables are not normalized, I would
normalize the tables first then worry about relating them.

All theories aside, there's no substitute to specific examples, so give us
some examples of the tables that you're working on.

HTH,
Immanuel Sibero