ninder 17:30 10 Mar 03

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 03

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.

Amazon Fire HD 8 review: A brilliant combination of function and value – with one massive caveat

1995-2015: How technology has changed the world in 20 years

How to create an introvert-friendly workplace

Apple Watch Series 2 review | Apple Watch 2 review: New Apple Watch is faster, brighter,…