Form/Query to search for product in any field

[jjake]

I have 1 Table.

tblProduct
Field 1 - ProductID
Field 2 - ProductName
Field 3 - RawProduct1
Field 4 - RawProduct2
Field 5 - RawProduct3

I input finished products & Raw products in the same table. Raw products are used to make the finished products.

I have a search form that opens a continuous form of all products.

Example Data.

Product 1 could use RawProduct1 and 2.
Product 2 could use RawProduct1 2 and 3
Product 2 could use Rawproduct2 and 3

The Goal - I have a form (frmProductSearch) with a combo box for Products. cboProduct. There is a button on the form cmdSearch. I would like to select one of the RawProducts from the list. It would then open a continuous form (frmProductList) which would display all products that contain said RawProduct. (And Also which RawProduct # It is)

I'm sure this is done with a query somehow but I wasn't sure which forum to post it in since I would like to use forms and a search feature.

Thanks.

 

[jdraw]

You should research Normalization if you are developing this database.

You might consider

Product and Ingredient where Ingredient represents your raw material/product.

So 1 Product may involve 1 or Many Ingredients. (2 tables)

This will make querying much easier.

Good luck.

 

[plog]

You need to structure your table correctly to do this. When you start numerating field names (RawProduct1, RawProduct2, ...). It's time for a new table.

From there you can then build a query and easily search to find what you want. So, put all those RawProducts in a seperate table like so:

tblRawProducts
RawProductID, autonumber, primary key of table
ProductID, number, foreign key to tblProduct
RawProduct, text, this will hold the value that is now in those numerated fields.

That's it. Now instead of 1 record with 5 fields for raw products, you would put 5 records into this new table.

 

[jjake]

Well one of my problems is that sometimes the finished product is also a raw product for another finished product. I found it easier not to duplicate data this way.

 

[plog]

In that instance you still need 2 tables, but the second one becomes super simple--it will just act like a traffic cop directing traffic to sort out what makes up each product:

Products
prod_ID, autonumber, primary key of table
prod_Name, text, name of product
prod_Finished, Yes/No, determines if this is a finished product
prod_Raw, Yes/No, determines if this is a raw product

ProductComponents
pc_ID, autonumber, primary key
pc_ProductFinished, number, foreign key to Products.prod_ID
pc_ProductRaw, number, foreign key to Products.prod_ID


Every raw and finished product you have goes into products. Then, ProductComponents holds what makes up every finished product in Products. All products with prod_Finished=true can be used in ProductComponents.pc_ProductFinished and all products with prod_Raw=true can be used in ProductComponents.pc_ProductRaw. That allows you to have your products serve the dual purpose of being a finished product in one instance and being a raw part of another product.

 

[jdraw]

There is a similar post in the forum. See the thread/dialog and see if it adds any clarification to your situation/options.