customer stock relationship

F

Fezz1954

I have a table of stock and I have a table of customers. Some of my
customers always order the same things. I want to join my tables so I can
create a report with the customer name ane what they order.

I only want one line in each table for each item.

Something like this:

Customer A chocolates
bread
milk

Customer B milk
eggs
newspapers
 
D

Douglas J. Steele

You need a third table to resolve the many-to-many relationship between the
two tables (A customer can order multiple stock items, a stock item can be
orderd by multiple customers)

This third table need only have the Customer ID and Stock Item ID in it,
although if they always order the same quantity, you'd likely want to store
that piece of information as well.

You'd probably also want a fourth table to represent the actual orders. This
would have Customer ID and Stock Item ID in it, as well as Order Date and
Order Quantity.
 
Top