Brainstorming help required...

[ukdane]

Come across a problem, and I need to brainstorm a solution, so if anyone has any ideas, any input is most welcome.

I'm currently designing a database to be used as a warehouse management system.
When an order is processed, and the goods are pulled from the warehouse, I need to be able to show the number of colli that have been pulled.

However we have some products that require sub products to be pulled (ie a frame is one colli, but the box of screws is another colli) both packages have the same product number.

I think the basic way to solve this, is to add create an additional product number for the under product ie product 3000 = frame, product 3000a = screws.

What I want to do is whenever an order is placed for product 3000, the sub products (in this instance 3000a) is automatically added to the order.

Any ideas how to achieve this?
The number of subproducts per product is variable.

(Access 2003)

[OBP]

First of all, can the Screws be withdrawn without the Frame or the Frame without the Screws?
I would definitely recommend a sub table of Under products with a One to Many relationship from the Pruduct table to the Underproduct table using the ProductID. In fact I would go so far as to change the name in the product table to Frame Assembly and delete the Screws from that table unless they can be selected on their own. In which case there wouldn't be any point in ordering them both together as you would end up with a surplus of Frames.

[Evocube]

I would suggest as you stated. Issue all of the sub products new numbers that associate with the main product. This will allow you to place individual order levels per product. I have acutally had to do this as each part was actually from a seperate supplier. The only way to avoid this situation is if the product is only sold as a "kit" deal. Example: you buy frame 3000 you get screws bundled with it. Issue from above is: screws 3000a are only used with frame 3000 no other products. If screws 3000a are used with frame 4000 then you have a seperate bin and the screws are renamed 4000a. So you would actually be stocking several "areas" with the same product to be able to reliably track the inventory. Plus if employees know they are the same they tend to pull form both.

[Bob Phillips]

I agree with OBP, a separate table of SubProduct is the way to go.

The spanner in the works is the assignation of the same product number to Frames and Screws (etc). Could that not be changed, it would make it simpler, then Screws could be pulled individually, or as part of Frames.

Of course, your query on a Product would have to also retriev all SUbProducts associated with it.

[ukdane]

The spanner in the works is the assignation of the same product number to Frames and Screws (etc). Could that not be changed, it would make it simpler, then Screws could be pulled individually, or as part of Frames.


That can be done, by adding the subproduct number to the end of the product number (ie adding an a, b, or c to the end of the number so product 3000 is associated with subproduct 3000a)

What I then need is some way for the database to automatically add x of 3000a to an order line, with the user adds x of product 3000.