items in set and individual items (1 Viewer)

[Ramnik]

Hello everyone,
I just want to know how to manage items in set and individual item.

suppose my product list are
individual items = A,B,C,D,E,F,G,H,I,J,K
and 5 pc set = A,B,C,D,F
and 3 pc set = G,H,K

how should i design the table . previously i designed the table for individual items and whenever orders for set is placed user had to enter individual items with quantity.
for e.g. order is for 5 pc set = 3000
A=3000
B=3000
C=3000
D=3000
F=3000

now i need to just say 5 pc set and it should be automatically populated.
and also if order have combination of individual and set items.

Thanks.

 

[billmeye]

In the table for products add an additional column IsSet as boolean for stating if the product is a set or just a single. Then, have a 2nd table tied to the product table that contains the list of items contained in each set.

First table:
individual items = A,B,C,D,E,F,G,H,I,J,K & SetA, SetB

Table2:
ProductID: this would be the SetA ID
Products: this would be each single Product ID for A,B,C,D,F

Then, when you are placing orders or whatever it is you are doing in the form, have an After Update event that then updates the item quantities for each single item.

If Me!IsSet = -1 Then
'adjust product quantities
End IF

 

[Ramnik]

Thanks for this solution. Can you tell me what would be the table structure (fields) for the second table ?
How to decide primary keys so that it will be easy to manage as i have nearly 1000 individual products and approx 25 different sets from these products.

Is there any solution in table design instead of using front end ????

Thanks.

 

[billmeye]

Table2:
PKID
ProductIDSet: this would be the Set ProductID from Products table
ProductID: this would be the single product ProductID from Products table

You could use Append queries if you don't like using VBA.