How to display relationships on a subform

[jasper]

Hi,



I'm creating a database to manage products that are ordered, delivered to and installed at our company. It consists of a number of tables based on suppliers, products and orders.

Most of the products we order are single items that delivered straight to our company, however, we have a small number of products that we order that need to be combined to make a final product before being delivered to us. We will order the products from each of the companies and, on the order form, will advise where the product is to be delivered to. The company that needs to combine the product is advised that a number of products are being delivered to them and they need to combine them before delivering to us.

For example:

A door is ordered from Company A
A door handle is ordered from Company B and delivered to Company A where company A installs the door handle on the door then delivers the product to us.

I have set up a Product Details form a number of tabs where users can enter and maintain detailed information about the product such as description, product codes, pricing, where the product is to be installed, the order history, etc. This is all working fine.

In the database we need to be able to dentify which products need to be combined - we are calling them product relationships. To manage this I've set up a Product Relationships form where users can select products that have relationships and these relatinships are assigned a unique identifier (PR1, PR2, etc). This populates a table - tblProductRelationships.

We also want to display these product relationships for a product on the Product Details form (if there are any). This is where I need help. I've set up a Product Relationships tab on this form and I'm trying to set up a subform that will display all relationships with other products for that product. I'm having trouble getting this part to work and am not even sure if setting up a subform is the correct approach.

Please note that there can be up to six products in a product relationship so I've allow for this on the Product Relationship form and table. Also, a product may be in more than one relationship. E.g. a door handle may be installed on a number of doors (all different products). We want to be able to view this information in the Product Details form.

My head is spinning with this one so any help will be muchh appreciated!

[June7]

Sounds like a manufacturing process. Not a new topic in forum. Search: manufacturing

Not a simple db to build.

[jasper]

Thanks for the tip June7. I will do a search on manufacturing and see what comes up.

We are revisiting the requirements due to the complexity of this db so I'll post the solution once we've worked it out.