Access Multiple value field Access 2007

V

Vanya

Joined
Jul 13, 2012
Messages
2
Reaction score
0
Hi,
I have the following problem. I want to create a little database in access 2007 to track some orders.

I have products table, Clients Table, Order Status etc.

So I have to create a table as follows

[Client Order No Type of Product NameofProduct Qnt DateofShipment etc...

I created a multiple value field for nameofProduct based on Products table. Because one order may have 10 products.

So if the the order consists of Product1, 2, 3 for one Order from 1 client I have to track the quantities for each product.

How can I set different quantity for each item in the multiple value field. It allows me to set 1 value in Quantity field and it appear for all the selected products. maybe I haven;t made the right thing.

Please help me. thank you
 
Hi,
Your products table should have only columns which are product properties "ID", "Name", "Description"....

Additionally I would create two more tables:
- "Orders" with columns "Order No", "ClientID", "ShipmentDate", "ShipmentAddress", etc,
- "OrdersProducts" where you link order numbers with product IDs, so three columns are necessary "OrderNo", "ProductID", "ProductQnt"

Now just add as many products with the same order number and different quantity for each product as you like to table "OrdersProducts".

Many tables for the first sight complicate the project, but if you consider it carefully, you'll see benefits of such a structure :)
 
goodfellow said:
Hi,
Your products table should have only columns which are product properties "ID", "Name", "Description"....

Additionally I would create two more tables:
- "Orders" with columns "Order No", "ClientID", "ShipmentDate", "ShipmentAddress", etc,
- "OrdersProducts" where you link order numbers with product IDs, so three columns are necessary "OrderNo", "ProductID", "ProductQnt"

Now just add as many products with the same order number and different quantity for each product as you like to table "OrdersProducts".

Many tables for the first sight complicate the project, but if you consider it carefully, you'll see benefits of such a structure :)
Click to expand...

Thank you very much, I'll try it.
 
Back
Top