Many-to-many relationship

[Guest]

I am trying to create a many-to-many relationship. I have 4 tables set
up--#1 has a customer id #; #2 has item id#; #3 has purchase order id# and
customer id; #4 has purchase order details with purchase order id and item
id. The PO Details table contains 2 key fields from 2 of the other tables.
I am only able to set up a one-to-one relationship--I cannot get create a
many-to-many relationship. What am I doing wrong?

 

[tina]

i assume you're talking about the many-to-many relationship between
tblPurchaseOrders and tblItems. you can't express a many-to-many
relationship between two tables directly, in Access. instead, you create a
third table ("child" table) to serve as a "link" between the two ("parent"
tables). you already did that, with your tblPurchaseOrderDetails. the
relationships are
tblCustomers (parent) 1:n tblPurchaseOrders (child)
tblPurchaseOrders (parent) 1:n tblPurchaseOrderDetails (child)
tblItems (parent) 1:n tblPurchaseOrderDetails (child)

if you're getting a relationship defined as 1:1 when you link two tables in
the Relationships window, that means that the Indexed property of the
foreign key field in the child table is set to "Yes (No Duplicates)". remove
the link in the Relationships window. then open the child table in design
view, and change the foreign key field's Indexed property to "Yes
(Duplicates OK)". save and close the table. then go back to the
Relationships window and create the link again.

hth