Is there a Customer table, or is all customer information in CustDetails?
Same thing for InvoiceDetails: is there an Invoice table? How do
ItemDetails fit into the picture? What are CityDetails?
Typically you would have a Customer table, an Invoice table, and a Products
(or Services) table. Each Invoice could include many products, and each
product could be included on many invoices, so there is a many-to-many
relationship between products and invoices. The InvoiceDetails table would
be a junction table is needed to resolve such a relationship. Having said
that, you could select the products that are to be line items on the invoice
and store them in the InvoiceDetails table without it being a junction
table. It makes the design a bit simpler, but limits your ability to track
sales of a particular product. In any case, on your main Invoice form you
would select a customer (storing only the CustomerID, but displaying other
Customer fields as needed. Basing the Invoice form on a query that includes
the Customer and Invoice tables is one way to do this, but in any case you
need to guard against allowing the customer information to be edited from
the Invoice form.
The Invoice form would have a subform based on InvoiceDetails. The subform
would contain a combo box based on the Products table. You would most
likely store only the ProductID and the price, again displaying other fields
as needed. Since the price is subject to change over time, you would want
the invoice to reflect the price at the time of purchase.
The Northwinds sample database is an example of a business database that
provides examples of what you are trying to do.
