help: Attaching several products to one customer

[kaira]

Hi, I'm not even sure how to describe my problem in the proper terms in order to look it up in any faq or tutorial.

I have one table, customers. Their name, number, etc.
Another table, products: Name, id, department, etc.
And the final table being orders. Customer bought product when, etc.

However, I'm only able to attach one product to each customer per order. I need to be able to attach any number of products, and I don't know how to go about doing that.

Also, when I create a form for entering all the data to these tables, the information doesn't stick. I create a form using the order table, enter the order and customer information, and have the products as a subform. The information will stick for each table, but they won't connect to each other.
If that makes any sense...

Any pointers would be great thanks, I don't even know where to start.

 

[pbaldy]

The common design here would include one more table. Your orders table would contain the basic information about the order: customer, date, etc. The new "OrderDetails" table would contain the product(s) on that order. It would have a record for each product, and each would include the key value from the order (like OrderID) and the other relevant info (ProductID, quantity, price, etc). The main form would be bound to the order table, the subform to the details table.

 

[ajetrumpet]

Any pointers would be great thanks, I don't even know where to start.

Would it be possible to post your file?? If not, that's OK. That might though, help with leading you in the right direction....

 

[Pat Hartman]

The Northwind database which ships with every copy of Access, is a basic order entry system. If you examine it, you can learn how your application needs to work.

 

[strods]

I can help you with it if you need as well... just say the word.

 

[Roscoe24]

Re-think your relationships!

Hi Kiara - Think this way ...

Think of an order you're taking in as a single entity, which contains MANY products.

Set up a table OrderHeader (abbreviated appropriately) which is AutoNumbered for each time you take in an order for a customer.

Set up a table OrderDetail (abbrev approp) which is autonumbered and related 1-00 to OrderHeader.

Create a form OrderHeader which pulls onto it (as a subform) Order Detail (tabulated). You can select from a combo, related to Product, as many Order Details per Order Headers as you want - all linked by a single OrderHeader. Each Order Detail will hold the quantity etc of each individual product required.

Hope this helps.