Multiple Many to many relationship question

[nycman]

Hi All,



I'm new in Access so help me solve this problem.

tblCustomr
CustomerID
CustomerName

tblCustomerOrder
CustomerOrderID
CustomerID
OrderID

tblOrder
OrderID
OrderName

tblOrderProduct
OrderID
ProductID

tblProduct
ProductId
ProductName


As you can see tblCustomerOrder & tblOrderProduct are Join tables for the rest of the tables. What I'm trying to do is that I need a form based on CustomerID with OrderID as Subform and ProductID as Sub Subform. So the ProductID is linked to the OrderID but I also need the ProductID to be linked to the CustomerID too

So CustomerID 1 will nave OrderID1 and will order ProductID1
Then the same Customer 1 will now place another order and now will get OrderID2 but will order the same ProductID1

The way they are linked now, if Customer 1 have OrderID1 and orders ProductID1 on the second order Product1 is already "assigned" to that same Customer1

Hope I've explained myself.

Thank you

[June7]

What problem? You haven't described a problem.

Work through tutorials at http://www.rogersaccesslibrary.com/

[nycman]

I already described my problem

"The way they are linked now, if Customer 1 have OrderID1 and orders ProductID1 on the second order Product1 is already "assigned" to that same Customer1"

[June7]

When I first viewed your post, there was not all that info.

I don't understand need for table tblOrder. I've never seen anyone name orders. Just put this field in tblCustomerOrder. Need OrderDate field.

Why can't customer order the same product on different orders? Why do you think that is an issue?

[nycman]

lol, sorry I posted the question before I filled the text. In my company we have proprietary names for orders so that is the reason. I understand about the OrderDate. What i'm saying now I have main order made from tblCustomer with a combobox on CustomerId to select a customer and a subform on tblCustomerOrders to show the Orders. In the CustomerOrder subform I have a datasheet form from tblOrderProduct with a combobox on ProductId for the user to select a product from tblProduct. As I said if I select CustomerID 1 and he has OrderID1 (Created in a separate form) and I add ProductID 1 & ProductID2, if that same customer 1 has another order (OrderID2) when I click on the little + sign next to that order I already see the ProductID1 & ProductID2 from the previous order. I somehow need to link the productID not just to the OrderId but also to the CustomerID

[June7]

I still don't understand what tblOrder is for. If this is a 1-to-1 relationship with tblCustomerOrder, merge these two tables into one.

"Click on the + sign" - I never use this. I don't understand why clicking on OrderID2 would show the details of OrderID1. Possibly the table relationships are not properly defined.

If you want to provide db for analysis, follow instructions at bottom of my post.

[nycman]

AttachedTest 1 Database.zip

[June7]

So the OrderName can be associated with more than one Order? Seems odd. Maybe a better concept is OrderCategory?

Perhaps link tblOrderProduct to tblCustomerOrder on the CustomerOrderID PK field.

[nycman]

Perhaps link tblOrderProduct to tblCustomerOrder on the CustomerOrderID PK field.

So I create FK of CustomerOrderID in the tblOrderCustomer? As I said I'm new how will this help me with what I need? How will the ProductId be linked to the CustomerID too? I'm just trying to understand better.

Thank you

[June7]

No, the FK field goes in tblOrderProduct which will link to the CustomerOrderID PK field in tblCustomerOrder.

The product is associated with customer through tblCustomerOrder.

You did not respond my question about OrderName.

[nycman]

So the OrderName can be associated with more than one Order? Seems odd.

Sorry..Hmm, you confused me there...the OrderName can be associated with more than one CustomerID not more than one Order

[June7]

I still don't know what you mean by 'order name'. What would be some actual order names?

Are you just trying to sequentially number orders for each customer? So customer1 would have Order1, Order2, Order3 and customer2 could have their Order1, Order2?

Still need to link tblCustomerOrder to tblOrderProduct

[nycman]

I still don't know what you mean by 'order name'. What would be some actual order names?

I'm sorry I see what you mean. I was making the tables as we speak here. We have more than few group of people that completes one order for the same customer, we call them Order Name (like who was the order name for this customer, Who were the groups that filled this order.). The group have names like Group 1, Group 2 etc. I agree with you and I will change the name.

So I linked the tables like you told me and now I'm stuck with how do I make the Form and the subforms populate what I need?

[June7]

So one order will have one ordername?

[nycman]

So one order will have one ordername?

One "Order" will have many groups working on it.

I renamed the tables and fields so it makes more sense. I also created a tblCustomerProduct as a Join table. But still confused of how to make this work with the forms.

Thank you