Access Multiple value field Access 2007


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
 
Joined
Jun 12, 2012
Messages
53
Reaction score
0
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 :)
 
Ad

Advertisements

Joined
Jul 13, 2012
Messages
2
Reaction score
0
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 :)
Thank you very much, I'll try it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top