Question Designing my very first database [Need a little help]

[ChrisSedgwick]

Hi all,

Recently my wife has started her own company. She designs and produces glitter products. She has asked me to set up some sort of system that will be able to

• Track orders - add new orders, see details about the orders and update them when they are shipped and paid for.
  
• Get reports - The only report she really needs is a list of all the orders just showing the products that have been ordered so she knows what she is working on.

The database just needs to be simple and user-friendly. I've been reading many forums for a few weeks since she asked me and I've also taken the time to read various tutorials, which have been very helpful.

I've so far created all the table I need and began work on some forms to make it easy for what she needs to do. Here's where I'm stuck...

I want to create a form that has the ability to add numerous products that may be different in type i.e wine glass or pint glass, and the colours for each. So that if a customer has ordered various different items, they can all be attached to the one customer.

Can this be done?

 

[MarkK]

I've so far created all the table I need

You should show what tables you have. I would expect to see product, order, order detail, customer, possibly supplier. I would also expect the product table to lookup data that different products might share, for instance, you might sell a shirt that comes in red, green, and blue, and you might sell it in size Sm, Md, Lg, and XL. That would be 3 x 4 = 12 different products, and the product table might then link to Model, Size, and Color.

 

[ChrisSedgwick]

Thanks for your response. At the moment I only have

Customers:
Customer ID
First Name
Last Name
Address
Contact No
Email

Products
Product ID
Product Name
Price

Orders
Order ID
Customer Name (Lookup field)
Product Name (Lookup field)
Colour (Lookup field)
Order Date
Delivery Method (Lookup field - Own values)
Pre Order (Check-box)
Paid (CB)
Complete (CB)
Delivered (CB)

Colours
Colour ID
Colour

This is where I've worked out that the orders table isn't going to be enough. This will obviously allow me to enter orders, along with all details regarding that one product. It's only now I've realised that I'll need to add various products to perhaps one customer, who may of course return again and purchase different products. I don't want to enter lines and lines of data on my orders table for just one customer, if it's the same order.

Is there anything else you'd need me to share from my database? I'm sorry it's brief, it's the first time I've used access to this extent. I do appreciate your help however.

Chris.

 

[ChrisSedgwick]

Here is a copy, I didn't realise I could add it as an attachment.

In what way will I need to link them if I created the tables that you mentioned - minus the sizes?

Would this allow me to add numerous products to one order at the same time?

 

[MarkK]

To make an order work you need an Order and an OrderDetail table. Order has fields like OrderID, OrderNumber, Date, PONumber, CustomerID, DeliveryAddress, ShipDate. OrderDetail has fields like OrderDetailID, OrderID, Quantity, ProductID, UnitPrice, Discount, and definitions of what sales taxes are applicable.

The Order table can be said to have a many-to-many relationship with the Product table where OrderDetail is the junction table, because one Product might exist on many Orders, and one Order might contain many Products.

OrderDetail will be the table with the most records in your system.

Hope this helps,

 

[ChrisSedgwick]

Thank you very much, I understand why 2 tables are needed now. I will try this.

Big help, thank you.

 

[MarkK]

You're welcome,

 

[spikepl]

Pay attention to naming of fields, obejcts. Follow MarkkkK's convention - no blanks! More here: http://www.access-programmers.co.uk/forums/showthread.php?t=225837

 

[ChrisSedgwick]

Hi MarKK,

I've now created the tables and it's all looking a lot better. My next question is..

I want to create a 'New Order' or 'Order Entry' form where when I I'm entering a new order for a customer, after completing all of the customer details, order dates etc, I want the ability to add the multiple products, along with their colours within that form.

So maybe like a button or command of some sort that will allow me to choose the different products being attached to the order.

Where would I begin with this?

Thanks
Chris

 

[MarkK]

Well, I would consider the color to be a property of a product, so I would define that in the products table. Consider that if you have bar codes and product numbers and so on, and you had to order new product from your supplier, you need to understand and define the difference between products independent of any order. This being the case, a blue product and a red product--even of the same model--are distinct products. Define that in your product table.

And that is an overall important process in database design, to determine which properties actually belong to which objects. Color is a property of a product, not a property of an OrderDetail, whereas the OrderDetail--you can see--definitely has Quantity as a property.

But now, more to your overall question, you need a subform. Your order form should show a single record, but your subform containing the OrderDetail will be displayed in datasheet mode (or a continuous form). This mimics your table relationships (and the real world), where one Order has many related OrderDetails. Design the subform, give it a RecordSource of OrderDetail, and save it. Open your Order form in design view, and drag the new OrderDetail subform and drop it on the Order form, and access will hookup the links automatically.

Hope that all makes sense,