Simple Sales DB

[outofpractice]

I know this is very easy, but I have not looked at access for about 7 years and am finding it very difficult.

All I am trying to create is a super simple sales database. I intended to have 3 tables: customer, product, orders and basically have a simple form where I would:

A. Select the customer from a list
B. in a sub-form/table be able to add as many products as the customer ordered (product info pulled form product table)
C. The customer and their products would be tied together with an autopopulating Order #

I am having so many problems doing this I have pulled out what hair I had left. It seems to be so simple, but I can not do this for some reason. I'm just thinking in circles and not making any progress. Any help is greatly appreciated!

 

[jdraw]

There is a free data model that may help here

http://www.databaseanswers.org/data_models/customers_and_products/index.htm

There are other models there as well that may be more appropriate.

If your looking for some refresher type info, the first 3 topics at this site are very informative and useful.
http://www.rogersaccesslibrary.com/forum/topic238.html

If you have specific questions, just post -- there are many people reviewing and offering advice.

 

[outofpractice]

Thanks for the response - I've looked at the link you supplied and set up some tables based on it. I believe I have this set up correctly but am not having any luck creating an entry form where you can select 1 Customer and add multiple products to 1 Order Number. Does this setup look correct?

 

[jdraw]

I have not done a lot of Order related work. My initial thought on your diagram is that Product price might change with Time. Seems you could include the Price/cost on the Order Item, or put some sort of Pricedate on the Product. Just if you have to refer back to an Order, the current price of product at that time may be different than that used in the Order. just a thought.

As for the question of adding multiple product for an Order, (not being directly involved this is my thinking). Poorly illustrated but hopefully the idea is there.

Select a Client,
if new order, create an Order
...................create orderItems
1................... add Product from a listbox or combo
???????? Finished
..........No Repeat 1
..........Yes
.......Next Client (repeat)