Order, Order

[Saxman]

As I said in a previous post, I am trying to combine a customer details
table with an orders table. Orders are only placed one at a time and
total orders over a period of time are not likely to be more than four.
(The product is large and expensive).

The relationship of customer needs to be one to many.

I am proposing two main tables, customer and orders.

I could for simplicity just have four fields for previous and current
orders on the customer table with other details, but it would be better
if there was a proper record of orders current and past.

Fields on the order table are quite simple.

Order number
Customer ID
First Name
Surname
Product
Order Date
Required Date
Completed Date
Cost
Paid/Unpaid
Salesman

In addition to a form with these fields, I would imagine that I would
require a form for product ID?

I do not need anything too complex, i.e sales, costs and all the other
palaver.

Excuse me for my inexperience, it's just that I'm not too good on
relationships ATM. I have tried to search for templates. There are
plenty for customer records, but no sales. The one's with sales tend to
be very complex.

TIA.

 

[tina]

okay, so you sell more than one product, correct? and one customer may order
more than one product - in a single order, or in separate orders, correct?
note, you should not "limit" your design by assuming that one customer will
only ever order "x" number of products - or only one product at a time - or
only one particular product.

first, you need to separate your entities into their own tables, and keep
the data about each entity in its' own table; for instance, the orders table
should not contain any *customer* data except the primary key value that
identifies the customer record. suggest the following, as

tblCustomers
CustID (primary key)
<other fields that describe a customer - do not include any data about
orders here>

one customer may have many orders, but each order belongs to only one
customer, so they have a one-to-many relationship:
tblCustomers.CustID 1:n tblOrders.CustID

tblOrders
OrderID (primary key)
CustID (foreign key from tblCustomers)
<other fields that describe an order *as a whole* - such as the date the
order was taken, perhaps the due date, perhaps a billing address>

one order may include many products, and one product may be part of many
orders, so you have a many-to-many relationship between orders and products.
you need a linking table to model this relationship, tblOrderDetails:
tblOrders.OrderID 1:n tblOrderDetails.OrderID
tblProducts.ProdID 1:n tblOrderDetails.ProdID

tblProducts
ProdID (primary key)
<other fields that describe a product - such as unit price. do not include
any data about orders here>

tblOrderDetails
DetailID (primary key)
OrderID (foreign key from tblOrders)
ProdID (foreign key from tblProducts)
<other fields that describe the details of this order for this product -
such as quantity. you may want to include unit price here (as well as in
tblProducts) because the price may change over time, and you want to
document what price was charged for this particular product on this
particular order. a more elaborate tables structure would include a
tblProductPrices to track the price of each product as it changes, and the
primary key from that table would be included in this table *instead of* the
ProdID>

hth

 

[Saxman]

okay, so you sell more than one product, correct? and one customer may order
more than one product - in a single order, or in separate orders, correct?
note, you should not "limit" your design by assuming that one customer will
only ever order "x" number of products - or only one product at a time - or
only one particular product.

Thank you Tina. I'll work on it.

I just downloaded Orders Management Database from the Access Help file.
It might suit my needs.

Thanks.