Sales Database - Questions?

[.Justin]

My mother has asked me to make her a Database which she can process orders through as her small part time home run business is getting a bit bigger. She wants a database which she can store products, customers and then orders.

I have the following Tables with the following fields:

Customers
Customer ID (Autonumber)
Title
Forename
Surname
Address
Postcode
Country
Telephone
E-mail

Orders
Order ID
Customer ID
Date of Order
Products Ordered - This is currently a memo feild where we are just entering the products
Date Dispatched
Date Shipped
Tracking ID
Order Status

Products
Product ID
Description
Quantity
Price

My question is how do I link it all together so I can allow her make an order by selecting the right customer, adding the product instead of writing the products in a memo. I also want the stock to no longer appear in the products list once its been sold as they are one of a kind products. So the deafult value when adding a product would be one but once its been in a order I want it to go to 0.

Thanks

 

[jdraw]

You may want to view and review these tutorials. The underlying theme is Customers,Orders, Items and involves pricing.
There are many design concepts in the material.
These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2


Also: Suggest you adopt a naming convention that does not have spaces or special characters in field and object names. You will save your self debugging and maintenance issues if you start with a good naming convention.

See http://www.accessmvp.com/strive4peace/ for a lot of great, useful tips and techniques.