ninder 17:30 10 Mar 2003

Hi all! this is very complicated to describe, but i will try my best! I have created a very simple database in which i have a customer table (CustID, Name etc) and Products tbl (ProductID, Product name etc)and have linked the 1 to many relationship with a orders table. the fields in this tbl are Invoice ID, Date, CustomerID, ProductID, Quantity and Amount. Now i wish to create a data entry form so that the customer can order many products (but it must contain a unique InvoiceID and date i.e. all those listed in the orders table). is this possible? As from this i wish to create a simple invoice with all the customers orders. I think i need a subform but am not sure. pls help!!!!!

  harristweed 20:02 10 Mar 2003

Each invoice can contain one or more items.
Each Product can be in many invoices.

You can't have a many to many relationship.

You need an aditional table that links the transaction each product, quantity etc, with invoice details.

Each transaction has one customer and one invoice, but possibly more that one product.

The main input form is based on a query that contains invoice, customer details. The product details are a subform that is based on a query linking the invoice with the product (transaction table).

Hope this is clear - just got back form the bar!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Photoshop CC 2018 released with new Curvature Pen and better brush tools

Camera tips to take better iPhone photos

Les meilleures applications de covoiturage 2017