Many-to-many relationship

[Ronnie]

I can't seem to set up a many-to-many relationship. These are my tables:

Customer - Customer ID (Primary Key)
Last Name
First Name
Etc.


Items - Item # (Primary Key)
Production Name
Manufacturer
Quantity
Amount


Purchase Order - Purchase Order ID (Primary Key)
Customer Last Name (Lookup set up from Customer ID)


PO Details - Purchase Order ID (Primary Key)
Items # (Primary Key)
Quantity


The Customer table has a one-to-many rela. with the Purchase order Table.
The Items table has a one-to-many rela. with the PO Details table.
When I try to set up a relationship with the Purchase Order table and the PO
Details table I can only get a one-to-one relationship.

What am I doing wrong?

 

[Lynn Trapp]

Ronnie,
You don't actually create a many-to-many relationship in the relationships
window. Instead, you create 2 one-to-many relationships between your 2
tables and the junction table. In your case, there appears to be only one
field for joining the [Purchase Order] table to the [PO Details] table, and
that is [Purchase Order ID]. That means they are, basically, sharing the
same key and that will always be a one-to-one relationship. You should add a
field to the [PO Details] table called [PODetailsID]. That will be your
primary key and then, [Purchase Order ID] will be the foreign key that joins
with the [Purchase Order] table.

--
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

 

[Tim Ferguson]

Purchase Order - Purchase Order ID (Primary Key)
Customer Last Name (Lookup set up from Customer ID)


PO Details - Purchase Order ID (Primary Key)
Items # (Primary Key)
Quantity
....
table. When I try to set up a relationship with the Purchase Order
table and the PO Details table I can only get a one-to-one
relationship.

One problem with the design is that you can't have the same item twice on
the same order... I can think of business scenarios where that may or may
not be appropriate.

Another problem is that I am suspicious of how you have set up the PK of
the PODetails table. Are you sure you have ONE key made of of TWO fields?
It sounds as though you have TWO UNIQUE KEYS made up of ONE field each;
this is why you would end up with a one-to-one relationship.

If you open the indexes window (in Table design, click the toolbar button
that looks like a lightning strike); it should look like

Key name FieldName
Primary Key PurchaseOrderID
ItemsNum
AnotherIndex AnotherField
etc etc

and when you put the cursor on the "Primary Key" line, the properties
should be Unique(yes) and Primary(yes) and IgnoreNulls(no).

Hope that helps


Tim F