Multiple Many to many relationship question

[June7]

Do you care which product which group entered/edit for the order?

[nycman]

Do you care which product which group entered/edit for the order?

I'm trying to understand your question. Do I care which Group Name entered which product and qty..yes.

[June7]

Then that info should be saved in tblOrderProduct, not tblCustomerOrder.

I still think need to link tblCustomerOrder and tblOrderProduct. Then tblOrder is just a lookup table for selecting group to associate with each product for an order.

[nycman]

Then that info should be saved in tblOrderProduct, not tblCustomerOrder.

I'm sorry but you are confusing me with your answers. TblProduct is linked to tblGroupProduct. My whole question was how do I link the ProductID with the CustomerID? And then second question is how do I produce the forms.

I already have many to many tblCustomer with tblGroup (One customer can have many groups working for them & one group can work for many customers). I also have the many to many tblGroup & tblProduct (one group can have many products & one product can have many groups). In my previous posts I attached my test database to show what I'm trying to do. I'm able to make a tblCustomer Form with tblCustomerGroup subform. I also have a tblGroupProduct subform in the tblCustomerGroup subform.

Here are the relationships for this project

I still think need to link tblCustomerOrder and tblOrderProduct. Then tblOrder is just a lookup table for selecting group to associate with each product for an order.

So link tblCustomerGroup with tblGroupProduct and leave everything else like it is?

[June7]

That confuses me even more. Where is tblCustomerOrder?

Consider:

tblProducts
ProductID
ProductName

tblGroups
GroupID
GroupName

tblCustomers
CustomerID
CustomerName

tblOrders
OrderID
CustomerID
OrderDate

tblOrderDetails
OrderID
ProductID
Quantity
GroupID

[nycman]

That confuses me even more. Where is tblCustomerOrder?

Consider:

tblProducts
ProductID
ProductName

tblGroups
GroupID
GroupName

tblCustomers
CustomerID
CustomerName

tblOrders
OrderID
CustomerID
OrderDate

tblOrderDetails
OrderID
ProductID
Quantity
GroupID

This is what I wrote in one of my previous posts and I attached a new Database

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

[June7]

I saw that post and looked at db. Still not sure about your data relationships. I show in previous post what makes sense to me.

[nycman]

I saw that post and looked at db. Still not sure about your data relationships. I show in previous post what makes sense to me.

But how do I relate the Product with the Customer? In my sample database if Customer1 is served by Group1 and Group 1 selected Product1, and then if Group1 is also used for Customer 2, Product 1 is already linked to Group 1 so it shows with Customer 2. In "normal" world after Customer 1 is served by Group 1 and they selected Product1 and then when Customer 2 is served by the same Group1 the Product should be empty, now I show product 1.

[nycman]

But how do I relate the Product with the Customer? In my sample database if Customer1 is served by Group1 and Group 1 selected Product1, and then if Group1 is also used for Customer 2, Product 1 is already linked to Group 1 so it shows with Customer 2. In "normal" world after Customer 1 is served by Group 1 and they selected Product1 and then when Customer 2 is served by the same Group1 the Product should be empty, now I show product 1.

Just please open my test database and select CustomerID 1 and then check under Group 2 you will see there is Product 1 selected. If you go to CustomerID 2 and open the same Group 2 you will see that Product1 is there. If you add Product2 there and go back to CustomerID1 and open Group2 again now there will be 2 products in there Product 1 & Product 2 instead of just one Product 1
CustomerID 1 should have Group 2 & Product 1 and CustomerID 2 should have Group 2 with Product1 & Product 2.

[June7]

I have looked at the db. The relationships do not make sense to me.

In my suggestion, product and customer are related through tblOrders and tblOrderDetails link. Build a query that includes all tables needed to display related data.

[nycman]

I have looked at the db. The relationships do not make sense to me.

lol..I know that is why I'm asking for help.

In my suggestion, product and customer are related through tblOrders and tblOrderDetails link. Build a query that includes all tables needed to display related data.

But again. I keep asking the same question.

- select CustomerID 1 and then check under Group 2 you will see there is Product 1 selected. If you go to CustomerID 2 and open the same Group 2 you will see that Product1 is there. If you add Product2 there and go back to CustomerID1 and open Group2 again now there will be 2 products in there Product 1 & Product 2 instead of just one Product 1
CustomerID 1 should have Group 2 linked to Product 1 and CustomerID 2 should have Group 2 linked to Product1 & Product 2.


tblProduct is linked with tblGroup. So every time I select/use Group 1 I will get the linked ProductID!

[June7]

You keep referencing your structure that isn't working. But should not be an issue with my suggested structure. Unless I really have zero understanding of these data relationships.

An example of data entry for a new order:

Main form bound to tblOrders.
Combobox on main form to select customer.
Subform bound to tblOrderDetails.
Comboxboxes on subform to select product and group.

Or can do 3 form levels.
Main form bound to tblCustomers
Subform bound to tblOrders
SubSubform bound to tblOrderDetails.
Comboboxes to select product and group.

Customer combobox would use tblCustomers as RowSource.
Product combobox would use tblProducts as RowSource.
Group combobox would use tblGroups as RowSource.

[orange]

Did you work through a tutorial from the link that June gave in post#2?
I haven't seen OrderName ever, either.
I recognize you are new to database and Access, and I would also suggest working through one of the tutorials.
It is often better to identify the business facts, create a data model (entity relationship diagram), test the model with test data and scenarios; then, once all is working, build the database using you model as a blueprint.

As for

But how do I relate the Product with the Customer?

Normally, Customer is related to Order and Product/Item is related to Order (often through OrderDetail).

See this for more info
Good luck.

[nycman]

Did you work through a tutorial from the link that June gave in post#2?

I really did! I even printed everything and read through it few times while going to work and back.

I haven't seen OrderName ever, either.

I recognize you are new to database and Access, and I would also suggest working through one of the tutorials.
It is often better to identify the business facts, create a data model (entity relationship diagram), test the model with test data and scenarios; then, once all is working, build the database using you model as a blueprint.

Thank you, I'll start from the beginning since obviously I'm missing a lot.

As for

Normally, Customer is related to Order and Product/Item is related to Order (often through OrderDetail).

See this for more info
Good luck.

This was not supposed to be a Customer, Order, Product/Item database but I realize that I can find the similarities with that kind of database and start from scratch. I think I started this post wrong from the beginning. I also think that my mind was so focused to what I was envisioning that I was not able to understand what June7 was telling me.

[nycman]

You keep referencing your structure that isn't working. But should not be an issue with my suggested structure. Unless I really have zero understanding of these data relationships.

June7,

I apologize but it was not my intention to insult you and your knowledge of Access. It is just that sometimes "seeing" the picture is hard especially hard for beginners.